Solved

# Day of Year from MS-SQL Query

Posted on 2009-07-14
1,402 Views
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?  Im using a SQL query to pull the data and calculate all in one step for export to Excel.

Jeff
0
Question by:jb7811
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 143

Expert Comment

ID: 24854069
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

LVL 4

Expert Comment

ID: 24854070

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

LVL 75

Accepted Solution

Aneesh Retnakaran earned 500 total points
ID: 24854095
SELECT DATEPART(dy, getdate() )
0

LVL 143

Expert Comment

ID: 24854135
khyer123, your link is for MySQL, the question is for MSSQL server.
0

LVL 60

Expert Comment

ID: 24854137
Aneesh's post is what you want for day of year in MS SQL.
0

Author Closing Comment

ID: 31603484
Thanks for the quick response!
0

LVL 60

Expert Comment

ID: 24854181
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
``````
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What â€¦
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wiâ€¦
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage emâ€¦
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
###### Suggested Courses
Course of the Month2 days, 15 hours left to enroll