devxlogo

Interoperable Dates and Times in SQL Queries

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'}
See also  5 Tips for Choosing the Right Medical Software for Your Practice
devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist