CODEWITHSUNDEEP
sqlt-sqldatetimetype-conversionvarchar
user9394033
user9394033

Reputation: 111

Conversion from varchar to Datetime

I have a date field with varchar data type and some of the values are for example 20220101000009CS. I need to show date as (MM/dd/yyyy HH:mm:ss') and tried the below query but getting the error 'Conversion failed when converting date and/or time from character string.'

select convert(datetime, 'DATE',20) 
from [TABLE1].[dbo].[ABC]

Please advice how do I get around this.

Upvotes: 0

Views: 832

Answers (2)

Tripp Kinetics
Tripp Kinetics

Reputation: 5459

How about something like this?

SELECT DATETIMEFROMPARTS(
    SUBSTRING(datefield, 1, 4),
    SUBSTRING(datefield, 5, 2),
    SUBSTRING(datefield, 7, 2),
    SUBSTRING(datefield, 9, 2),
    SUBSTRING(datefield, 11, 2),
    SUBSTRING(datefield, 13, 2),
    0)
FROM [TABLE1].[dbo].[ABC];

Upvotes: 0

Thom A
Thom A

Reputation: 95949

You can use STUFF to inject the needed characters in the right place to get the format yyyyMMdd hh:mm:ss which is also unambiguous in SQL Server:

SELECT V.YourColumn,
       CONVERT(datetime2(0),STUFF(STUFF(STUFF(LEFT(V.YourColumn,14),13,0,':'),11,0,':'),9,0,' '))
FROM (VALUES('20220101000009CS'))V(YourColumn);

Upvotes: 3

Related Questions