ODBC - Date and Time

> ODBC - Open Database Connectivity

1 - About

You have several options when dealing with dates and times using ODBC:

  • Default ODBC Date formatting
  • Specific SQL function
  • ANSI date format
  • Default database date format
Advertising

3 - Options

3.1 - Default ODBC Date formatting

You can use the default ODBC Date formatting:

#03-DEC-81#

Which is the style that DAO/Jet will use by default.

3.2 - Specific SQL function

You can use a SQL function if you are directly executing a SQL statement to encapsulate whatever date format you want. For example with the TO_DATE() Oracle function:

SELECT TO_CHAR(sysdate,'MM/DD/YYYY') FROM dual

This will be brought over then as a character string containing the date format you specified.

3.3 - ANSI date format

You can use an ANSI date format via an ODBC escape:

{ d '1981-12-03' }

Note that this is enclosed in single quotes and always of the format YYYY-MMDD.

3.4 - Database default date format

You can always use the database standard date format in much the same way.

Example for Oracle:

{ '03-Dec-1981' }

Time may be specified as follows:

{ t 'hh:mm:ss' }

A timestamp (date including time) can be done with the following format:

{ ts '1981-12-03 00:00:00' }
Advertising