Search code examples
sqlsql-serverdatetimejulian-date

Julian Date to Datetime SQL Server

I have the following columns in a table

Year    Julian_Day  Time_HHMM   Seconds Decimal_Day
2015    271             2000       0        0.7415013

The ouput datetime is "2015-09-28 20:00:00"

I tried to figure out how to convert those parameters to datetime but I havent had luck. Any help is welcome

Solution

  • You should be able to do this by nesting calls to the DATEADD function for the various components:

    SELECT DATEADD(SECOND, [Seconds], DATEADD(MINUTE, CAST(RIGHT([Time_HHMM], 2) AS INT), DATEADD(HOUR, CAST(LEFT([Time_HHMM], 2) AS INT), DATEADD(DAY, [Julian_Day] - 1, DATEADD(YEAR, [Year] - 1900, 0)))))
FROM   YourTable

    Note I have not used the Decimal_Day column.

    DATEADD takes integer parameters for the components, so you may need some additional casting on the columns to INT, if they are currently of type VARCHAR.