I (regrettably) possess some dates which were saved into varchar posts. These posts contain dates within the following format:
I have to import these values right into a table that is set to datetime and formats dates such as this:
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
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.