CODEWITHSUNDEEP
sqlsql-serverdatetime
Divya Rose
Divya Rose

Reputation: 225

Converting varchar to date in SQL Server

I have a varchar column which has values like "Aug 07 2017, 04:14 AM,EDT".

I need to convert this to a date column so that its possible to take the maximum value of the date.

I tried this:

select CONVERT(datetime, @dateField, 108)  
from table

But I am getting the following error:

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

Upvotes: 1

Views: 4768

Answers (3)

The beginner
The beginner

Reputation: 622

Here is a solution:

select CONVERT(datetime, REPLACE(LEFT(@dateField, LEN(@dateField) - 3),',','') ,108 )

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can just use left() and convert():

select convert(date, left('Aug 07 2017, 04:14 AM,EDT', 11))

If you want a datetime then convert the date and time separately, then:

select ( convert(datetime,
                 left('Aug 07 2017, 04:14 AM,EDT', 11)
                ) +
         convert(datetime,
                 convert(time,
                         substring('Aug 07 2017, 04:14 AM,EDT', 14, 8)
                        )
                )
       )

Note: This is not taking the time zone into account.

Upvotes: 2

Ladislav Oros
Ladislav Oros

Reputation: 26

Better Datetime solution

DECLARE @dateField AS NVARCHAR(50) = 'Aug 07 2017, 04:14,EDT'
-- Get index of last comma
DECLARE @validDateField AS NVARCHAR(20) = REPLACE(LEFT(@dateField, LEN(@dateField)- CHARINDEX(',', reverse(@dateField))), ',','')
SELECT CONVERT(DATETIME, @validDateField, 108)

Upvotes: 0

Related Questions