ODBC provides three escape clauses for date, time, and timestamp values.
| Category | Shorthand syntax | Format |
|---|---|---|
| Date | {d 'value'} | "yyyy-mm-dd" |
| Time | {t 'value'} | "hh:mm:ss" |
| Timestamp | {Ts 'value'} | "yyyy-mm-dd hh:mm:ss[.f…]" |
The format of dates has more of an impact on Oracle applications than on SQL Server applications. Oracle expects the date format to be “DD-MON-YY”. In any other case, the TO_CHAR or TO_DATE functions are used with a date format model to perform a format conversion.
Microsoft SQL Server automatically converts most common date formats, and also provides the CONVERT function when an automatic conversion cannot be performed.
As shown in the table, ODBC Extended SQL works with both databases. SQL Server does not require a conversion function. Nevertheless, the ODBC shorthand syntax can be generically applied to both Oracle and SQL Server.
| ODBC Extended SQL | Oracle | Microsoft SQL Server |
|---|---|---|
| SELECT SSN, FNAME, LNAME, BIRTH_DATE FROM STUDENT WHERE BIRTH_DATE < {D '1970-07-04'} |
SELECT SSN, FNAME, LNAME, BIRTH_DATE FROM STUDENT WHERE BIRTH_DATE < TO_DATE('1970-07-04', 'YYYY-MM-DD') |
SELECT SSN, FNAME, LNAME, BIRTH_DATE FROM STUDENT WHERE BIRTH_DATE < '1970-07-04' |