Convert varchar to date.

Question

I (unfortunately) have some dates that were saved into varchar columns. These columns contain dates in the following format:

mmddyy

For example:

010110

I need to import these values into a table which is set to datetime and formats dates like this:

2010-09-17 00:00:00.000

How can I convert the string above into a datetime value below?

Answer 1

The CAST function will do this, the problem is that it will assume you first 2 digits are year. This should for for you:

SELECT CAST((RIGHT('010110',2) + LEFT('010110',4)) AS DATETIME)

This is assuming that all dates are MMDDYY.

Answer 2

Use substring to grab the relevant parts into a 'yyyy-mm-dd' format, then cast it to datetime:

cast(
    '20' + substring(col1,5,2) + '-' +
    substring(col1,1,2) + '-' +
    substring(col1,3,2)
    as datetime)

Answer 3

Here's a solution

SELECT CAST(SUBSTRING(@date, 5, 2) + SUBSTRING(@date, 1, 4) AS DATETIME)

Answer 4

Get the string into YYMMDD format and you should be in good shape:

declare @x varchar(6)
set @x = '091710'

declare @d datetime

set @d = cast(RIGHT(@x,2) + LEFT(@x,4) as datetime)

select @d

Answer 5

That the supported date conversion styles are described in MSDN. The bad news is that there is no style for mmddyy. So you'll have to do a custom formating. How that is done depends on how you import. Is it an SSIS ETL step? Is it a one time table copy?

You can custom convert the format you specify straight from T-SQL:

declare @x varchar(6) = '010110';

select dateadd(month, cast(substring(@x, 1,2) as int)-1,
    dateadd(day, cast(substring(@x,3,2) as int)-1,
    dateadd(year, cast(substring(@x,5,2) as int),'01-01-2000')));

Answer 6

try something like this:

DECLARE @OldTable table (col1 int, col2 char(1), col3 char(6))
DECLARE @NewTable table (col1 int, col2 char(1), col3 datetime)
INSERT @OldTable VALUES (1,'A','010110') --mmddyy = jan  1, 2010
INSERT @OldTable VALUES (1,'A','091710') --mmddyy = sep 17, 2010

INSERT INTO @NewTable
        (col1, col2, col3)
    SELECT
        col1, col2, RIGHT(col3,2) + LEFT(col3,4) --<< cast to datetime not needed... 
        FROM @OldTable                           --<< because @NewTable.col3 is datetime
        ORDER BY Col1

SELECT * FROM @NewTable

OUTPUT:

col1        col2 col3
----------- ---- -----------------------
1           A    2010-01-01 00:00:00.000
1           A    2010-09-17 00:00:00.000

(2 row(s) affected)

Answer 7

Not an answer on it's own...but you may want to consider turning this into a user defined function for future use. Makes referring to this date field much simpler in the future.