Database DateTime types

In AMT there are 3 Date/Time database types, they are:

Type:	Default Format:	Example string:	Initial Value:
DbsDate	CCYY-MM-DD	1900-01-01	DATE'<Date format>' \| '<Date format>' \| CURRENT_DATE
DbsDateTime	CCYY-MM-DD HH:MM:SS[.NNNNNNN]	2020-12-26 13:37:22.2565128	TIMESTAMP'<DateTime format>' \| '<DateTime format>' \| CURRENT_TIMESTAMP
DbsTime	HH:MM:SS[.NNNNNNN]	12:34:56.78901234	TIME'<Time format>' \| '<Time format> \| CURRENT_TIME

For the types with time, the number of fractional seconds can be set with the length property in the Object Inspector, with seven being the maximum number of fractional seconds that can be set.

When inserting or updating data in a Database Date Time type field, the data must be formatted according to the correct format for the type used.
See the table above for the default formats.

The Initial value format is also dependent on the length property value in the case of DbsDateTime and DbsTime for the amount of fractional seconds.

For static initial value dates and times, an optional type keyword is allowed, e.g. DATE'2021-07-23'.

For dynamic initial value dates and times, the following functions can be used for their corresponding types: CURRENT_DATE, CURRENT_TIMESTAMP and CURRENT_TIME.
These will insert the current Date/Time at the moment of the record creation.

See the 'Initial Value' column in the table above for an overview.


Changing date types or date formats to an other date type, date format, or to any other type, while having data in them will cause the data to become invalid.

Code example DbsDate, DbsDateTime length 3 & DbsTime length 3 :

begin_definitions
var

    datevar  : date

    tq_date  : tablequery (t_dates)

end_definitions

routine main
begin_routine

    datevar.datetime   := today.datetime

    tq_date.custno   := 1
    tq_date.custname   := 'alpha'
    tq_date.dbsdate     := '2020-12-11'
    tq_date.dbsdatetime := '2020-12-12 07:08:09.123'
tq_date.dbstime     := '20:21:22.234'
    tq_date.insert ()
    tq_date.clear ()

    tq_date.custno   := 2
    tq_date.custname   := 'beta'
    tq_date.dbsdate     := datevar.ccyy-mm-dd
    tq_date.dbsdatetime := datevar.datetime
    tq_date.dbstime     := datevar.time
    tq_date.insert ()
    tq_date.clear ()

end_routine

Return to

Fields