INF: Converting SQL CHAR Values into DATETIME Values

Last reviewed: April 25, 1997
Article ID: Q69133

The information in this article applies to:

  - Microsoft SQL Server version 4.2 for OS/2
  - Microsoft SQL Server version 4.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 query words: Transact-SQL Windows NT
Keywords : kbprg SSrvTran_SQL SSrvWinNT
Version : 4.20 | 4.20
Platform : OS/2 Windows

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 25, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.