CODEWITHSUNDEEP
sqlsql-server
M0rty
M0rty

Reputation: 1085

Change a column of string dates to datetime SQL Server

I currently have a varchar column with 400 entries. Each row is formatted like this:

'Thu, 18 Sep 2014 23:59:06 '

Is it possible to change this varchar column to datetime?

I have tried this...

ALTER TABLE BE
ALTER COLUMN pubDate datetime

But I get the error

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

Upvotes: 0

Views: 4168

Answers (2)

Denise
Denise

Reputation: 2005

I would create a new datetime column, then write an update statement to insert the correct values, then drop the old column (if desired).

The update statement would look something like this:

update be set be.dt=convert(datetime, be.pubDate)

edit: just about to say the same as okaram in the comments. You probably have to fix your data to be in a format SQL server accepts. Yours is pretty close to 113 if you get rid of the weekday at the beginning and add milliseconds at the end. You need it to look like this:

23 Oct 2016 11:02:07:577

so

update be set be.dt=convert(datetime, 
CONCAT(SUBSTRING(be.pubDate, Charindex(', ', be.pubDate), LEN(be.pubDate)),
':000'), 113)

Upvotes: 5

JonasB
JonasB

Reputation: 458

the correct format for datetime is:

YYYY-MM-DD hh:mm:ss

Change your entries and it should work.

Upvotes: 0

Related Questions