# Thread: How to make DATE from day of year and year value?

1. Registered User
Join Date
Mar 2007
Posts
212

## Unanswered: How to make DATE from day of year and year value?

Hi

Does SQL Server have any function to make a date from a given year and day-of-year value?

I suppose am looking for a function similar to the MySQL MAKEDATE(year,dayofyear) function.

2. Registered User
Join Date
May 2009
Posts
509
Provided Answers: 1
ozzii, you can try DATEADD.

SELECT DATEADD(DAY, 167, '2009' + '-01-01')

167 is the Julian day of the year.
2009 is the Year.
Create a date for Jan 1 of the supplied year and add the number of Julian days to it.

PS. Correction: This would actually be off by 1 day. you would need to subtract 1 from your number of days to get the correct date.

SELECT DATEADD(DAY, 167 - 1, '2009' + '-01-01')
Last edited by Stealth_DBA; 11-30-09 at 14:53.

3. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
Provided Answers: 12
Code:
```DECLARE @year        int
, @day_of_year int

SET @year = 2009
SET @day_of_year = 152

SELECT DateAdd(yy, @year - 1900, 0) As yy
, DateAdd(dd, @day_of_year, 0) As dd
, DateAdd(dd, @day_of_year, DateAdd(yy, @year -1900, 0)) As done```
This allows you to pass integer values (so no type casting) as parameters.

4. Registered User
Join Date
May 2009
Posts
509
Provided Answers: 1
gvee, Yes, datatypes was a concern which you have dealt with (nicely). However, you solution has the same problem as my original one. Since the OP wants to convert a Julian day to an actual date, taking the day part of the Julian day and adding it offsets the date by 1 day. This is easily seen if you use 200901 as the Julian day.

Using your query if the Year is 2009 and the Day_of_Year is 1, you would be 2009-01-02 as the result when it should be 2009-01-01. 1 needs to be subtracted from the Day_of_Year to correct this.

Code:
```DECLARE @year        int
, @day_of_year int

SET @year = 2009
SET @day_of_year = 152 - 1  <<< subtract 1 day

SELECT DateAdd(yy, @year - 1900, 0) As yy
, DateAdd(dd, @day_of_year, 0) As dd
, DateAdd(dd, @day_of_year, DateAdd(yy, @year -1900, 0)) As done```
Other than that, you solution works very well.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•