Day of Year from MS-SQL Query

I'm working on a query that will need to do some calculations relating to days of inventory remaining based on current level and year-to-date usage.  To do this I need to be able to pull out the current day of year (1 - 365) so that I can use this to help determine days of inventory remaining.  What is the best way to do this calculation based on the value retrieved from GetDate?  Im using a SQL query to pull the data and calculate all in one step for export to Excel.

Thanks in advance,
Jeff
jb7811Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
SELECT DATEPART(dy, getdate() )
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
is this what you are looking for?
datepart(year, yourdatevalue)

or the start of current year?
convert(datetime, cast(datepart(year, getdate()) as varchar(4)) + '-01-01', 120)
0
 
khyer123Commented:
check out this page: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

You want to get the date, and then do DATE_FORMAT(date, format), where the format you want is %j for "Day of Year"
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
khyer123, your link is for MySQL, the question is for MSSQL server.
0
 
Kevin CrossChief Technology OfficerCommented:
Aneesh's post is what you want for day of year in MS SQL.
0
 
jb7811Author Commented:
Thanks for the quick response!
0
 
Kevin CrossChief Technology OfficerCommented:
And to be accurate since some years are 366 days versus 365, you can use this to calculate the number of days left in the current year based on getting the day of the year of 12/31 of current year minus day of year of the current date as shown by Aneesh.
SELECT DATEPART(dy, DATEADD(yy, DATEDIFF(yy, 0, GETDATE())+1, -1)) - DATEPART(dy, GETDATE()) AS RemainingInventoryDays

Open in new window

0
All Courses

From novice to tech pro — start learning today.