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