convert numeric to DateTime-CodePudding

I need to convert numeric into DateTime Datatype in SQL Query,

here is my input,

18300031121994

this is in %H%M%S%d%m%Y format,

expected output,

1994-12-31 18:30:00.000

how can i convert numeric value to date time using SQL Query... please help me

CodePudding user response：

This should do the job for you:

declare @dt bigint = 18300031121994
, @dtStr nchar(14)

set @dtStr = right('000000'   cast(@dt as nvarchar(14)),14) -- leading zeros would not appear in an int, so we have to lpad with 0s to avoid midnight issues

select @dtStr
,cast( right(@dtStr, 4)   '-'   substring(@dtStr, 9, 2)   '-'   substring(@dtStr, 7, 2)   ' '   left(@dtStr, 2)   ':'   substring(@dtStr, 3, 2)   ':'   substring(@dtStr, 5, 2) as datetime)

We first convert from a numeric datatype to a string, so we can do string manipulation.

We then left pad with 0s to account for any which would have been "truncated" through leading zeros not being part of the original integer value.

We then cut the string into its component chunks based on the given pattern HHmmssddMMyyyy, and put these back together with appropriate delimeters.

We then cast this yyyy-MM-dd HH:mm:ss formatted string back to a datetime.

Per Larnu's comment above though; it's better if you don't need to do this / work out why you have dates like this to deal with in the first place (though maybe you're migrating data from a legacy system / something like that, where this is an unavoidable step).