Search code examples
mysqlsqltype-conversionvarcharsmalldatetime

Cast different parts of a varchar into a date time, SQL

I have a varchar that is an 8 digit number and I need to convert to a datetime. The production number is an automatically generated number from when the time the order was placed. For example, the production number 10090203 is actually the datetime 2015-10-09 02:03:00. I need to cast a list of the varchar numbers into a datetime so that I can cross compare it to a list of date times. Here is how I convert datetime into varchar, but I am not sure how to go the other way around.

 SELECT RIGHT('0' + CAST(DATEPART(M, table1.coldatetime) AS varchar), 2) 
+ RIGHT ('0' + Cast(DATEPART(DD, table1.coldatetime) AS varchar), 2) 
+ RIGHT('0' + CAST(DATEPART(HH, table1.coldatetime) AS varchar), 2)
+ RIGHT('0' + CAST(DATEPART(MINUTE, table1.coldatetime) AS varchar), 2)
AS 'CreatedNumber' FROM table1
Solution

  • This should work for you:

    SELECT   
    DATEADD(mi,CAST(SUBSTRING(table1.coldatetime,7,2) AS INT),DATEADD(hour,CAST(SUBSTRING(table1.coldatetime,5,2) AS INT),CONVERT(datetime,'2015' + LEFT(table1.coldatetime,2)+SUBSTRING(table1.coldatetime,3,2))))
FROM
    table1