CODEWITHSUNDEEP
sqlsql-serverdatetime
Seth Reno
Seth Reno

Reputation: 5450

Most concise way to convert Julian date (yyyy[day of year]) to SQL datetime

I'm working with an existing database where all dates are stored as integers in the following format: yyyy[3 digit day of year].

For example: 

2010-01-01 == 2010001
2010-12-31 == 2010365

I'm using the following SQL to convert to a datetime:

DATEADD(d, 
    CAST(SUBSTRING(
            CAST(NEW_BIZ_OBS_DATE AS VARCHAR), 5, LEN(NEW_BIZ_OBS_DATE) - 4
        ) AS INT) - 1, 
    CAST('1/1/' + SUBSTRING(CAST(NEW_BIZ_OBS_DATE AS VARCHAR),1,4) AS DATETIME))

Does anyone have a more concise way to do this?

Upvotes: 1

Views: 6483

Answers (2)

DVK
DVK

Reputation: 129481

I think at least one simplification can be made:

DATEADD(d, 
    NEW_BIZ_OBS_DATE % 1000 - 1, 
    CAST('1/1/' + SUBSTRING(CAST(NEW_BIZ_OBS_DATE AS VARCHAR),1,4) AS DATETIME)
)

The second term could also be done as follows I think.

    CAST('1/1/' + CAST(NEW_BIZ_OBS_DATE/1000 AS VARCHAR) AS DATETIME)

Upvotes: 0

Guffa
Guffa

Reputation: 700562

You can do it numerically instead of usings strings:

dateadd(day, NEW_BIZ_OBS_DATE % 1000 - 1,
  dateadd(year, NEW_BIZ_OBS_DATE / 1000 - 2000, '2000-1-1')
)

(Note that 2010356 is not 2010-12-31 but 2010-12-22. 2010365 is 2010-12-31.)

Upvotes: 2

Related Questions