DATE( ) function

Extracts the date from a specified date or datetime and returns it as a character string. Can also return the current operating system date.

Syntax

DATE(<date/datetime> <,format>)

Parameters

date/datetime

Optional. Datetime. The field, expression, or literal value to extract the date from. If this parameter is omitted, the current operating system date is returned.

format

Optional. Character. The format to apply to the output string. If this parameter is omitted, the current ACL date display format is used. You cannot specify a format if you have omitted the date/datetime parameter. The format parameter must be enclosed in quotation marks.

Output

Character.

Remarks

The length of the output string is always 12 characters, or 24 characters in the Unicode edition of ACL. If the specified output format, or the ACL date display format, is less than 12 characters (or 24 characters), the output string is padded with trailing blank spaces.

The DATE( ) function, when used without parameters, returns the current operating system date as a character string, whereas the TODAY( ) function returns the current operating system date as a datetime value.

A field specified for the date/datetime parameter can use any date or datetime format, as long as the field definition correctly defines the format.

When specifying a literal date or datetime value for the date/datetime parameter, you are restricted to the formats in the table below, and you must enclose the value in backquotes – for example, `20141231`. For datetime values, you can use any combination of date, separator, and time formats. The date must precede the time, and you must use a separator between the two. Times must be specified using the 24-hour clock. Offsets from Coordinated Universal Time (UTC) must be prefaced by a plus sign (+) or a minus sign (-). Do not use any separators such as slashes (/) or colons (:) between the individual components of dates or times.

Date formats

Separator formats

Time formats

Examples

YYYYMMDD

single blank space

hhmmss

`20141231`

YYMMDD

the letter ‘t’

hhmm

`20141231 235959`

the letter ‘T’

hh

`141231 2359`

 

+/-hhmm

(UTC offset)

`141231t23`

+/-hh

(UTC offset)

`141231t235959-0500`

(Note: Do not use hh alone in the main time format with data that has a UTC offset. For example, avoid: hh+hhmm. Results can be unreliable.)

`141231T2359+01`

If you use the format parameter to control how the output string is displayed, you can use any supported ACL date display format. For example:

The format parameter must be specified using single or double quotation marks – for example, "DD MMM YYYY".

Examples

Example Return value

DATE(`20141231 235959`)

20141231

Returned as a character string, using the current ACL date display format

DATE(`20141231 235959`, "DD-MMM-YYYY")

31-Dec-2014

Returned as a character string, using the specified format

DATE( )

The current operating system date returned as a character string, using the current ACL date display format

DATE(Receipt_timestamp)

The date returned as a character string for each value in the Receipt_timestamp field, using the current ACL date display format

DATE(Receipt_timestamp, "DD/MM/YYYY")

The date returned as a character string for each value in the Receipt_timestamp field, using the specified format

Related reference
DATETIME( ) function
TIME( ) function
TODAY( ) function


(C) 2013 ACL Services Ltd. All Rights Reserved. | Send feedback