Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2007

    Unanswered: Dateadd function

    For SQL Server 2005 running on MS Server 2003 Standard x64 Edition.

    I am not a SQL DBA but have been asked to help with the following issue.

    The following command converts a julian date in the format of xyy.ddd to a date/timestamp in the format of ‘yyyy-mm-dd 00:00:00.000’. This statement works correctly for each month’s procedure except when January’s data is processed because January’s data also contains data from the prior year for December.

    update dbo.JOBLIST
    set DateRead=convert( varchar(10), dateadd( day, (convert(int,substring(DateRead_julian,5,3) -1 ) ), '20110101') , 120)

    Example: 2 records

    1st has julian date of 111.003 – This is correctly converted to ‘2011-01-03 00:00:00.000’.

    2nd has julian date of 110.364 - This is converted to ‘2011-12-30 00:00:00.000’. This is correct except that year should be 2010.

    How can the above command be modified to correctly calculate the year?

    Any guidance/help would be greatly appreciated. Thanks

  2. #2
    Join Date
    Nov 2004
    Provided Answers: 4
    The base date was hard coded as '20110101'. It should depend on the yy part of the Julian date.
    select DATEADD(day, CAST(RIGHT(DateRead_julian, 3) AS int)-1, 
           CONVERT(datetime, substring(DateRead_julian, 2, 2) +
              '0101', 112))
    With kind regards . . . . . SQL Server 2000/2005/2012

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Aug 2007
    I will give this a try. Thank you very much.

Posting Permissions

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