Most concise way to convert from date format: yyyy[3 digit day of year] to SQL datetime
- by Seth Reno
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 == 2010356
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?
