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?

Improve company productivity with a Business Account.Sign Up

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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.