Results 1 to 4 of 4
  1. #1
    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. #2
    Join Date
    May 2009
    Posts
    508
    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. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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.
    George
    Home | Blog

  4. #4
    Join Date
    May 2009
    Posts
    508
    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
  •