Search code examples
sqldate-conversion

SQL date conversion year/datepart to datetime

I am trying to convert from "tick" date format to a datetime format. Example:

0000113326 to 2013-11-22 00:00:00.000

I know how to go the opposite way:

SELECT '00001' + RIGHT(CAST(YEAR(StartDate) AS varCHAR(4)), 2) + RIGHT('000' + CAST(Datepart(dy, StartDate) AS VARCHAR(3)), 3)
FROM table

I just need to reverse engineer it.

Any ideas?

Solution

  • Something like this?

    DECLARE @tick varchar(20) = '0000113326'
SELECT dateadd(d, cast(right(@tick, 3) as int) - 1, '20' + substring(@tick, 6, 2) + '0101')