Convert varchar to date

Question

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

mmddgg

For example:

010110

I need to import these values ​​into a table that is set to datetime, and formats the dates as follows:

2010-09-17 00: 00: 00.000

How to convert the string above to the datetime value below?

Answer 1

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

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

All dates are assumed to be MMDDYY.

Answer 2

Here is the solution

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

Answer 3

Get the string in 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 4

Use the substring to capture the corresponding parts in the format "yyyy-mm-dd", then cast it to datetime:

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

Answer 5

That the supported date conversion styles are described on MSDN . The bad news is that there is no style for mmddyy . Thus, you will need to make your own formation. How to do this depends on how you import. Is this an SSIS ETL step? Is this a one-time copy of the table?

You can customize the format that you specify directly 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

This will work for a 6-character varchar string

 CAST((RIGHT('130513',2) + ltrim(rtrim(right(left('130513', 4), 2))) + LEFT('130513',2) ) AS DATETIME) 

This will convert the string until May 13, 2013.