Convert 20200324003032 to date and tim

Solution 1:

MS Sql Server

In MS Sql Server the first 8 digits can be cast to a datetime.

And the last 6 digits can be stuffed, then casted to a datetime.

Adding the 2 together will create a datetime that has both the date & time. Which can then be converted to the ODBC datetime format.

select *
, try_convert(varchar(19), 
    cast(left(datetime_number,8) as datetime) +
    cast(stuff(stuff(right(datetime_number,6),5,0,':'),3,0,':') as datetime)
    , 120) as datetime_stamp
from (values 
  (20200324003032)
, (cast(format(getdate(), 'yyyyMMddHHmmss') as decimal(14))) 
) test(datetime_number)

db<>fiddle here

PostgreSQL

In PostgreSQL it's a bit easier.
Since it just requires an extra space to cast it to a timestamp.

select *
, regexp_replace(datetime_number::text,'^(\d{8})(\d{6})$','\1 \2')::timestamp as ts
from (values 
  (20200324003032)
, (to_char('now'::timestamp, 'YYYYMMDDHH24MISS')::bigint)
) test(datetime_number);

db<>fiddle here

MySql

It's really simple in MySql.

select *
, timestamp(datetime_number)  as ts
from ( 
  select 20200324003032 as datetime_number
  union all select now()+0
) test;

db<>fiddle here