Date and time columns

Date and Time columns are a very complicated part of AQT because:

people want the flexibility to view them in a particular format.
most databases are very fussy about how dates are specified in insert/update statements.
all databases have different date/time field types, and different rules about how they are handled.

Display Format versus Update Format

AQT uses two different formats for date/time columns – the Display Format and the Update Format. The Display Format is the way you wish to see the dates displayed. Whenever AQT is displaying a table, it will display the dates in this format. AQT will use the Update Format when you are specifying a date in an Update/Insert Statement or in a Where clause. This happens in two places:

when you do a Get Values on a date/time column you will get it in the Update Format
when you go into Update mode on a table AQT will change the date/time columns from the Display format to the Update format.

You set the Display Format with Options > Display Options > Format of Date/Time Columns. The Update Format is hard-coded within AQT, based on the database type (see the section Date data types below).

Date data types

The following discussion summarises the date / time / timestamp data-types of the various databases. In this:

DB2

Type	Stores	Required Format
Date	Date	yyyy-mm-dd
Time	Time	hh.nn.ss or hh:mm:ss
Timestamp	Date, Time to the microsecond	yyyy-mm-dd-hh.nn.ss.dddddd

In the Timestamp column, the Date and Time parts are connected by a minus sign. This is unlike most other databases, which use a blank.

Oracle

Type

Stores

Required Format

Datetime

Date, Time to the second

Date specified in the format of NLS_DATE_FORMAT.

Time specified in ??? format.

AQT will set the NLS_DATE_FORMAT to the format you want to display the dates. This allows you to use the same format for displaying dates as for specifying in Where clauses (in other words, the Display Format and Update Format will be the same).

Sybase Enterprise and MS SQL Server

Type	Stores	Required Format
Datetime	Date, Time to millisecond	yyyy-mm-dd hh:nn:ss.ddd
SmallDateTime	Date, Time to the minute	yyyy-mm-dd hh:nn
Timestamp	?

Informix

Type	Stores	Required Format
DATE	Date	As specified with the DBDATE or GLS_DATE environment variables. If these are not specified then mm/dd/yyyy.
DATETIME A TO B (example: DATETIME Year TO Second)	Flexible, depending on column definition.	As specified with the DBDATE / DBTIME or GLS_TIMESTAMP environment variables.

As AQT is unable to detect the values of DBDATE and other environment variables, you must specify in Format of Informix date/time literals the how the date and time literals are to be formatted.

MS Access

Type	Stores	Required Format
Datetime	Date, Time to second	yyyy-mm-dd hh:nn:ss

When specifying DateTime literals, MS Access requires these to be enclosed in #s (not single-quotes like every other database). AQT will do this automatically.

Date Functions

Many databases provide functions for specifying date/time values.

in Oracle this is TO_DATE('2001-12-31 15:32:55', 'yyyy-mm-dd hh24:mi:ss')
in Informix this is DATE("12/31/2000") for Date values or DateTime(2001-12-31 15:32:55) Year to Second for Datetime values

In these databases it is normal to use these functions for specifying Date/time values. In some cases, date/times can only be specified using such functions.

AQT can automatically include these functions when a date/time value to be given. In the Run SQL (and other) Windows, a checkbox Include TO_DATE is shown when you are dealing with a Datetime column. If you select this, AQT will include the TO_DATE (or other) function with the Date/time value.

Dates in Parameters

In AQT you can use parameter markers in queries:

Select * from DEMO.EMPLOYEE WHERE HIRE_DATE = ?

When you run this you will be prompted for the value of Hire Date. You must specify a correct Date/Time value for it. This value this must be in the Update format. Functions such as TO_DATE cannot be used in this situation.

With Oracle there is a further complication with queries like these – due to limitations with the Oracle ODBC driver, AQT does not know the "type" of the parameter. You will be shown that the parameter is Char(100) or Varchar2(999) rather than Datetime.

Suppression of Time-part

Oracle, Sybase, SQL Server and Access do not have a separate date type. Dates are normally stored in Datetime columns, with a zero time-part.

When AQT sees a zero time-part it will not display it; AQT will display the column as a date.

Example:

rather than display the column as "2001-12-31 00:00:00", AQT will display it as "2001-12-31". If the time-part is non-zero AQT will display it.

The one exception to this is when you are updating a date; in that situation AQT will include the time part in case you wish to update this.