How can I convert numeric(16) formatted as yyyyMMddHHmmss00 to Datetime in SQL

Question

I want to convert start_date numeric(16) to datetime

for example:

2023032012121201 to Datetime

I tried this

SELECT CONVERT(datetime, SUBSTRING(CAST(start_date AS varchar(16)), 1, 8) + ' ' + 
      STUFF(STUFF(STUFF(RIGHT('0000000000' 
      + CAST(start_date AS varchar(16)), 10), 3, 0, ':'), 6, 0, ':'), 9, 0, '.')) 
 FROM table

but it gives Conversion failed when converting date and/or time from character string error

Output should be: 2023-03-20T12:12:12.01

Answer 1

If the value is a numeric(16,0) (or a varchar(16)), then you can inject the needed characters ( , : and .) into the needed positions and then TRY_CONVERT that to a datetime2(2). This turns the value into the format yyyyMMdd hh:mm:ss.nn which is an unambiguous date and time format in SQL Server:

DECLARE @YourString numeric(16,0) = '2023032012121201';
SELECT @YourString,
       TRY_CONVERT(datetime2(2),STUFF(STUFF(STUFF(STUFF(@YourString,15,0,'.'),13,0,':'),11,0,':'),9,0,' '));

I use TRY_CONVERT because you might have bad data; storing date and time values in anything other than a date and time data type throws validation out the window. As such any such values will return NULL instead. Likely you will also want to find and correct such values before you fix your design and change the data type to a date and time data type (datetime2(2) seems the right choice here, and why I used it).