Interoperable Dates and Times in SQL Queries

Many developers using ODBC, JDBC, OLE DB, and ADO are unaware that there is a standards-compliant solution for expressing dates, times, and timestamps in SQL statements. When writing queries in proprietary SQL, developers find that porting their SQL code to a different platform often produces syntax errors. The Open Group, and subsequently the International Standards Organization, published standards that enable developers to express dates, times, and timestamps in an interoperable manner. Developers can use escape sequences with ODBC and JDBC drivers for Microsoft SQL Server, Oracle, Informix, IBM DB2, Sybase, and other SQL databases.

From the “Using Interoperable SQL” chapter of Database Magic with Ken North (Prentice-Hall PTR):

To specify a date, time, or timestamp escape sequence, substitute a value expression in the following clauses. The escape sequences for date, time, and timestamp values are:

 {d'value'}			express a date value{t'value'}			express a time{ts'value'}			express a timestamp value.

Example:

 UPDATE PERSONSET BIRTHDATE={d '1953-12-23'}
Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

Recent Articles: