Convert varchar column to datetime

Question

In SQL Server, I have a varchar column with value such as:

 07/May/2012:00:06:22
 07/May/2012:00:06:22
 07/May/2012:00:06:22
 07/May/2012:00:06:22
 07/May/2012:00:06:22

• How do I convert this column to a datetime column with real datetimes?
• If this is not possible, how do I just create a new column and copy this values into it but as datetime values?

Answer 1

Assuming dates in other months are truncated to 3-char months:

 ALTER TABLE dbo.tablename ADD newcol DATETIME;

 UPDATE dbo.tablename
   SET newcol = CONVERT(DATETIME, 
    REPLACE(LEFT(varchar_col, 11), '/', ' ') 
        + ' ' + RIGHT(varchar_col, 8), 113);

Once you've verified, you can drop the varchar_col, rename newcol, and then re-add any affected indexes.

Answer 2

Likely you have some data that doesn't fit the datetime column somewhere in the file. Insert to a staging table with a varchar or navachar field for the column and look at the data. You may need to adjust some of it (or null out bad records) before loading to the real table.

Answer 3

If you can reformat your CSV to 07/May/2012 00:00:00, it will work. SQL Server doesn't like the colon between date and time.

Otherwise, I agree with @HLGEM - you need a staging table.

Answer 4

Try the following:

select convert(datetime,(left('07/May/2012:00:06:22',11) + ' ' + 
        right('07/May/2012:00:06:22',8)))

Against a whole table, it would look like:

select convert(datetime,(left(YourColumn,11) + ' '
      + right(YourColumn,8)))

Or:

select convert(datetime,stuff('07/May/2012:00:06:22',12,1,' '))

select convert(datetime,stuff(YourColumn,12,1,' '))

Answer 5

1-. you can check this post: https://stackoverflow.com/questions/1509977/convert-varchar-into-datetime-in-sql-server

someone has the same problem and could solved it with this:

DECLARE @Date char(8)
set @Date='12312009'
SELECT CONVERT(datetime,RIGHT(@Date,4)+LEFT(@Date,2)+SUBSTRING(@Date,3,2))

OUTPUT:

-----------------------
2009-12-31 00:00:00.000

(1 row(s) affected)

2-. if you want to add the same value to a new column , first create the column with an

ALTER TABLE MyTable ALTER COLUMN NullCOl DATATYPE NOT NULL;

and after that you can make

UPDATE TABLE MyTable SET NewCol=CONVERT(datetime,RIGHT(@Date,4)+LEFT(@Date,2)+SUBSTRING(@Date,3,2)) //replace the last part for the code that you use to convert from varchar to date.

Regards