?
Solved

Day of Year from MS-SQL Query

Posted on 2009-07-14
7
Medium Priority
?
1,410 Views
Last Modified: 2012-05-07
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
0
Comment
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
  • Learn & ask questions
7 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
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

by:khyer123
ID: 24854070
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
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 24854095
SELECT DATEPART(dy, getdate() )
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24854135
khyer123, your link is for MySQL, the question is for MSSQL server.
0
 
LVL 60

Expert Comment

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

Author Closing Comment

by:jb7811
ID: 31603484
Thanks for the quick response!
0
 
LVL 60

Expert Comment

by:Kevin Cross
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

Open in new window

0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question