Date and Time Functions

Show TOC

Function	Description	Syntax	Examples
DATE	Returns the date specified by the given year, month, and day values in the default format.	`DATE(year,month,day)`	`DATE(2005,4,23)` Compiles the year, month and day values into a formatted date. In this case, the following might be returned: `23/4/2005`
TIME	Returns the time specified by the given hour, minutes and second values in the default format.	`TIME(hour,min,sec)`	`TIME(14,30,45) == 14:30:45` Compiles the current time according to the hour, minute and second values into a formatted time. In this case, the following might be returned: `14:30:45`
DADD/ TADD	Increases/decreases a date/time value by the specified number of date (`dt`) units.	`DADD(dt,n,unit)` `TADD (t,n,unit)` where `unit` is one of the items listed in the Date and Time Units table	`TADD(@CREATE_TIME,13,'H')` Adds 13 hours to the value returned. In this case, `10:17:22` would be converted to `23:17:22` (default HH:NN:SS format).
DGET TGET	Returns the numeric value of the selected part of a date (`dt`) or time `(t)`.	`DGET(dt,part)` `TGET(t,part)` where `part` is one of the items listed in the Date and Time Parts table	`DGET(@CREATE_DATE,'M')` Returns only the value of the specified time unit from within the date value. In this case, `30.05.2002` would return `5,` while `27.02.2004` would return `2`
DSTR/ TSTR	Converts a date or time object format to a defined text string.	`DSTR` `(dt,mask)` `TSTR (t,mask)` where `mask` is devised from the set of special characters listed in the Date and Time Masks table	`DSTR(@DATE_FIELD,'MON DD, YYYY')` Converts the date value returned to a string date format. In this case, `30.05.2002` would be converted to `May 30, 2002` `DSTR(DADD(@CREATE_DATE,13,'D'),'XML_DATE')` Using the DADD function, this expression adds 13 days to the returned date, and then converts the date format to the XML standard format. In this case, `30.05.2002` would be converted to: `2002-06-12`
DSUB/ TSUB	Returns the difference between two dates, in the specified date unit.	`DSUB(dt1,dt2,unit)` `TSUB (t1,t2,unit)` where `unit` is one of the items listed in the Date and Time Units table	`DSUB(NOW(),@CREATE_DATE,'D')` Calculates the difference in the two dates, specified here, in days. In this case, values of `16.09.2007` (today) and `05.8.2007` for these two fields would return `39`
DVAL/ TVAL	Converts a date or a time string representation – usually in a user input field – to a standard date/time value. The date/time input format is defined by the client platform and user locale settings.	`DVAL(str)` `TVAL(str)`	`DVAL(@TIME_FIELD)` Converts a date string to a standard input format. For example, `30.05.06` would be converted to the standard date format: `30/05/2006`
NOW/ TNOW	Returns the current date or time.	`NOW()` `TNOW()`	`NOW()` Returns the current calendar date, such as: `27/06/2006` `TNOW()` Returns the current time, such as:`08:40:10`

Function

Description

Syntax

Examples

DATE

Returns the date specified by the given year, month, and day values in the default format.

DATE(year,month,day)

DATE(2005,4,23)

Compiles the year, month and day values into a formatted date. In this case, the following might be returned: 23/4/2005

TIME

Returns the time specified by the given hour, minutes and second values in the default format.

TIME(hour,min,sec)

TIME(14,30,45) == 14:30:45

Compiles the current time according to the hour, minute and second values into a formatted time. In this case, the following might be returned: 14:30:45

DADD/

TADD

Increases/decreases a date/time value by the specified number of date (dt) units.

DADD(dt,n,unit)

TADD (t,n,unit)

where unit is one of the items listed in the Date and Time Units table

TADD(@CREATE_TIME,13,'H')

Adds 13 hours to the value returned. In this case, 10:17:22 would be converted to 23:17:22 (default HH:NN:SS format).

DGET

TGET

Returns the numeric value of the selected part of a date (dt) or time (t).

DGET(dt,part)

TGET(t,part)

where part is one of the items listed in the Date and Time Parts table

DGET(@CREATE_DATE,'M')

Returns only the value of the specified time unit from within the date value. In this case, 30.05.2002 would return 5, while 27.02.2004 would return 2

DSTR/

TSTR

Converts a date or time object format to a defined text string.

DSTR (dt,mask)

TSTR (t,mask)

where mask is devised from the set of special characters listed in the Date and Time Masks table

DSTR(@DATE_FIELD,'MON DD, YYYY')

Converts the date value returned to a string date format. In this case, 30.05.2002 would be converted to May 30, 2002

DSTR(DADD(@CREATE_DATE,13,'D'),'XML_DATE')

Using the DADD function, this expression adds 13 days to the returned date, and then converts the date format to the XML standard format. In this case, 30.05.2002 would be converted to: 2002-06-12

DSUB/

TSUB

Returns the difference between two dates, in the specified date unit.

DSUB(dt1,dt2,unit)

TSUB (t1,t2,unit)

where unit is one of the items listed in the Date and Time Units table

DSUB(NOW(),@CREATE_DATE,'D')

Calculates the difference in the two dates, specified here, in days. In this case, values of 16.09.2007 (today) and 05.8.2007 for these two fields would return 39

DVAL/

TVAL

Converts a date or a time string representation – usually in a user input field – to a standard date/time value. The date/time input format is defined by the client platform and user locale settings.

DVAL(str)

TVAL(str)

DVAL(@TIME_FIELD)

Converts a date string to a standard input format. For example, 30.05.06 would be converted to the standard date format: 30/05/2006

NOW/

TNOW

Returns the current date or time.

NOW()

TNOW()

NOW()

Returns the current calendar date, such as: 27/06/2006

TNOW()

Returns the current time, such as:08:40:10