INF: Converting SQL CHAR Values into DATETIME Values

ID Number: Q69133

1.10 1.11 4.20

OS/2

Summary:

The following information discusses how to convert CHAR values into

DATETIME values so that a SMALLINT value can be subtracted and a

START_TIME value can be obtained.

More Information:

To begin with, the character date and time must be converted into the

SQL Server datetime format. Then the SMALLINT value can be subtracted

to obtain START_TIME. Both tasks can be performed in a single SQL

expression; however, it is easier to understand the process if both

tasks 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 column

named "term_date" with a format of YYMMDD, these values can be

converted 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 or

subtract a given amount of seconds, minutes, days, and so forth. For

example, assuming the SMALLINT value ("@delta") is in seconds, the

following function will return the start date/time:

dateadd(ss,-(@delta),???)

The entire expression can be put into the following select statement

so it is executed for each row in the input table. "@delta" is assumed

to be an input parameter. If "@delta" is in units other than seconds,

the first parameter of dateadd must be changed to reflect the correct

units (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

Additional reference words: 1.10 1.11 4.20 Transact-SQL