I (regrettably) possess some dates which were saved into varchar posts. These posts contain dates within the following format:

mmddyy

For instance:

010110

I have to import these values right into a table that is set to datetime and formats dates such as this:

2010-09-17 00:00:00.000

How do i convert the string above right into a datetime value below?

The CAST function is going to do this, however , it'll assume you initially 2 numbers are year. This will for for you personally:

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

This really is presuming that dates are MMDDYY.

Here is a solution

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

Obtain the string into YYMMDD format and you ought to be who is fit:

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

declare @d datetime

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

select @d

Use substring to seize the appropriate parts right 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)

The supported date conversion styles are referred to in MSDN. Unhealthy news is the fact that there's no style for mmddyy. So you will need to perform a custom formating. How that's done is dependent how you import. Could it be an SSIS ETL step? Could it be a 1 time table copy?

You are able to custom convert the format you specify completely 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')));

try something similar to 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)

No answer by itself...but you might want to consider turning this right into a user defined function for future use. Makes mentioning up to now area easier later on.