snippetsqlMinor

Convert varchar column to datetime

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

convertvarchardatetimecolumn

Problem

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?

Solution

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.

Code Snippets

ALTER TABLE dbo.tablename ADD newcol DATETIME;

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

Context

StackExchange Database Administrators Q#17904, answer score: 8

Revisions (0)

No revisions yet.