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