1

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
Cheddar
  • 530
  • 4
  • 30
  • Are you allowed to change the DDL? I mean, it is all well and good to use the date time as part of an identifier, but why not store the actual date and time as a datetime in the same table? (After thought: I see that you already are, with coldatetime. So why can't you just use that?) – cobaltduck Oct 27 '15 at 13:43
  • We are currently doing a variety of software updates to try and move everything over to datetime to get rid of the varchar entries, due to the fact they dont include a year and we will eventually have duplicates, but these tables are incredibly long and we don't want to have 2 columns dedicated to the same information. Especially if one column will eventually show duplicates because it is lacking the year. Also, the varchar type fields are from very old data tables before we even had an SQL server. They were being stores in an access program and now we are moving to vb.net. – Cheddar Oct 27 '15 at 13:48
  • @Cheddar - How do you want to pick the year? – Adish Oct 27 '15 at 13:58
  • This query I am writing is just for data collection so I can cast the year as 2015. – Cheddar Oct 27 '15 at 14:02

2 Answers2

2

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
Adish
  • 709
  • 4
  • 12
0
Select 
 STR_TO_DATE(CONCAT('2015',coldatetime,'00'),'%y','%m','%d','%H','%i','%s')
From Table1
Andy
  • 49,085
  • 60
  • 166
  • 233
Jim Ward
  • 529
  • 1
  • 4
  • 5