How to convert CHAR values into DATETIME values in SQL Server

Extended support for SQL Server 2005 ended on April 12, 2016

If you are still running SQL Server 2005, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

This article was previously published under Q69133
The following information discusses how to convert CHAR values intoDATETIME values so that a SMALLINT value can be subtracted and aSTART_TIME value can be obtained.
To begin with, the character date and time must be converted into theSQL Server datetime format. Then the SMALLINT value can be subtractedto obtain START_TIME. Both tasks can be performed in a single SQLexpression; however, it is easier to understand the process if bothtasks are considered separately.

For example, if the time value is stored in a column named "term_time"with a format of HHMMSS, and the date value is stored in a columnnamed "term_date" with a format of YYMMDD, these values can beconverted to the SQL Server datetime with the following call:
   convert(datetime,term_date+" "+          substring(term_time,1,2)+":"+          substring(term_time,3,2)+":"+          substring(term_time,5,2)   )				

Given a date in SQL Server datetime format, it is easy to add orsubtract a given amount of seconds, minutes, days, and so forth. Forexample, assuming the SMALLINT value ("@delta") is in seconds, thefollowing function will return the start date/time:

The entire expression can be put into the following select statementso it is executed for each row in the input table. "@delta" is assumedto be an input parameter. If "@delta" is in units other than seconds,the first parameter of dateadd must be changed to reflect the correctunits (minutes, days, and so forth).
   select  dateadd( ss, -(@delta), convert(datetime,term_date+" "+               substring(term_time,1,2)+":"+               substring(term_time,3,2)+":"+               substring(term_time,5,2)   ))     from t1				
Transact-SQL Windows NT

Article ID: 69133 - Last Review: 12/01/2005 00:21:49 - Revision: 4.2

Microsoft SQL Server 4.21a Standard Edition, Microsoft SQL Server 6.0 Standard Edition, Microsoft SQL Server 6.5 Standard Edition, Microsoft SQL Server 7.0 Standard Edition, Microsoft SQL Server 2000 Standard Edition, Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Express Edition, Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Enterprise Edition, Microsoft SQL Server 2005 Workgroup Edition

  • kbprogramming KB69133