Database DateTime types
You can use three database datetime types in AMT:
| 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 |
To set the number of fractional seconds for time types, use the length property in the Object Inspector. You can set a maximum of seven fractional seconds.
Format your data according to the type requirements when you insert or update database datetime type fields.
The following table shows the default formats for each type.
The initial value format also depends on the length property value for DbsDateTime and DbsTime to determine the number of fractional seconds.
For static initial values for dates and times, an optional type keyword is allowed, e.g., DATE'2021-07-23'.
For dynamic initial values for dates and times, you can use the following functions for their corresponding types:
CURRENT_DATE, CURRENT_TIMESTAMP, and CURRENT_TIME.
These functions insert the current date/time when you create the
record.
See the 'Initial Value' column in the table above for an overview.
|
Changing date types or date formats to another 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, and DbsTime length 3
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