How to convert YYMMDDhhmm formatted NVARCHAR into YYYY-MM-DD hh:mm:ss formatted DATETIME?-CodePudding

For example:

The value 2208111603(nvarchar) needs to be converted to 2022-08-11 00:00:00(datetime).

The things I have tried and the corresponding errors I've got so far as follow:

CAST(DATE_TIME AS numeric) works but CAST(CAST(DATE_TIME AS numeric) AS datetime) returns Arithmetic Overflow Error.

convert(datetime2, CAST(DATE_TIME AS numeric), 105) returns

Explicit conversion from data type numeric to datetime2 is not allowed

CONVERT(datetime2, DATE_TIME , 105) returns

Conversion failed when converting date and/or time from character string.

CONVERT(datetime2, CONVERT(CHAR(10), DATE_TIME), 105) returns

Conversion failed when converting date and/or time from character string.

Finally, I tried PARSE(DATE_TIME AS date USING 'en-US') which resulted in

Error converting string value '2208111603' into data type date using culture 'en-US'.

The SQL Server's CONVERT function also has some built-in formats as a third parameter for converting varchar to datetime. So, I tried SELECT convert(datetime,'2208111603',112) which resulted in the same error (Msg 241):

Conversion failed when converting date and/or time from character string.

Any help would be much appreciated!

CodePudding user response：

With just a bit of string manipulation...

Note: this will assume century of 20

Example

Declare @S varchar(50) ='2208111603'

Select try_convert(datetime,stuff(stuff(@S,7,0,' '),10,0,':'))

Results

2022-08-11 16:03:00.000