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. The tables also include the column Literal Format which is how the date / time / timestamp value must be specified to the database.

In these, MM is the month, HH is the 24-hour, mm is the minute.

DB2

Type	Stores	Literal Format	Examples
Date	Date	yyyy-MM-dd	'2010-12-31'
Time	Time	HH:mm:ss or HH.mm.ss	'15:32:55'
Timestamp	Date, Time to the microsecond	yyyy-MM-dd HH:mm:ss.dddddd but also accepts yyyy-MM-dd-HH.mm.ss.dddddd	'2010-21-31 15:32:55.123456'

Oracle

Type

Stores

Literal Format

Examples

Date

Date, Time to the second

Date specified in the format of NLS_DATE_FORMAT.

If a time part is coded, the TO_DATE function must be used.

'2010-12-31'

TO_DATE('2010-12-31 15:32:55', 'yyyy-mm-dd hh24:mi:ss')

Timestamp

Date, Time to the microsecond

yyyy-MM-dd HH:mm:ss.dddddd

'2010-12-31 15:32:55.123456'

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).

TO_DATE function

When specifying Oracle dates, it is generally best to use the TO_DATE function. This removes any ambiguity about how the date is specified. The TO_DATE function must be used if the time part of a datetime value is included in the literal.

Example: TO_DATE('2010-12-31 15:32:55', 'yyyy-mm-dd hh24:mi:ss')

In many places in AQT, there is an option Use TO_DATE. When is checked, AQT will automatically use the TO_DATE function when specifying the literal

TO_TIMESTAMP

Timestamp literals can be specified in the format as given in the table above. If you wish to specify the timestamp literals using another format, you do this with the TO_TIMESTAMP function.

Example: TO_TIMESTAMP('2010-31-12 15:32:55.123456', 'yyyy-dd-mm hh24:mi:ss.ff')

MS SQL Server

Type	Stores	Literal Format	Examples
Datetime	Date, Time to millisecond	yyyy-MM-dd HH:mm:ss.ddd	'2010-21-31 15:32:55.123'
SmallDateTime	Date, Time to the minute	yyyy-MM-dd HH:mm	'2010-21-31 15:32'
Time	Time to the billionths of a second	HH:mm:ss.ddddddddd	'12:34:56.123456789'
Timestamp	System timestamp when row inserted	The value cannot be explicitly set

Sybase ASE

Type	Stores	Literal Format	Examples
Datetime	Date, Time to millisecond	yyyy-MM-dd HH:mm:ss.ddd	'2010-21-31 15:32:55.123'
SmallDateTime	Date, Time to the minute	yyyy-MM-dd HH:mm	'2010-21-31 15:32'
Time	Time to the microseconds	HH:mm:ss.dddddd	'12:34:56.123456'
Timestamp	System timestamp when row inserted	The value cannot be explicitly set

Informix

Type

Stores

Literal Format

DATE

Date

As specified with the DBDATE or GLS_DATE environment variables. If these are not specified then mm/dd/yyyy.

Can also use DATE('12/31/2010')

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.

Can also use DATETIME('2001-12-31 15:32:55') YEAR TO SECOND

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	Literal Format	Example
Datetime	Date, Time to second	yyyy-MM-dd HH:MM:ss	#2010-21-31 15:32:55#

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.

SQL/MX and SQL/MP

Type	Stores	Literal Format	Examples
Date	Date	DATE 'yyyy-MM-dd HH:mm:ss.ddd'	DATE '2010-21-31'
Time	Time	TIME ' yyyy-MM-dd HH:mm'	TIME '15:32'
Timestamp	Date, Time to microsecond	TIMESTAMP 'HH:mm:ss.ddddddddd'	TIMESTAMP '2010-21-31 15:32:55.123456'

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

For datetime or timestamp columns, when the time-part is zero, AQT will not display it. In other words, AQT will display a value of '2001-12-31 00:00:00' as '2001-12-31'. This is useful if you are holding dates (with a zero time) in these columns.

This behaviour is governed by Options > Display Options > Show Time part of timestamp when zero. When this option is selected, you will see the full value of the timestamp column.