DATETIME( ) function

Converts a datetime to a character string. Can also return the current operating system datetime.

Syntax

DATETIME(<datetime> <,format>)

Parameters

datetime

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

format

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

Output

Character.

Remarks

The length of the output string is always 27 characters, or 54 characters in the Unicode edition of ACL. If the specified output format, or the ACL date and time display formats, are less than 27 characters (or 54 characters), the output string is padded with trailing blank spaces.

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

When specifying a literal datetime value for the datetime parameter, you are restricted to the formats in the table below, and you must enclose the value in backquotes – for example, `20141231 235959`. 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 235959`

YYMMDD

the letter ‘t’

hhmm

`141231 2359`

the letter ‘T’

hh

`141231t23`

 

+/-hhmm

(UTC offset)

`141231t235959-0500`

+/-hh

(UTC offset)

`141231T2359+01`

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

If you use the format parameter to control how the output string is displayed, you are restricted to the formats in the table below. You can use any combination of date, time, and AM/PM formats. The date must precede the time. Placing a separator between the two is not required as ACL automatically uses a single space as a separator in the output string. The AM/PM format is optional, and is placed last. The format parameter must be specified using single or double quotation marks – for example, "DD-MMM-YYYY hh:mm:ss AM".

Date formats

Time formats

AM/PM formats

Examples

all supported ACL date display formats

hh:mm:ss

none

24-hour clock

"DD/MM/YYYY hh:mm:ss"

hhmmss

AM, or PM

12-hour clock

"MMDDYY hhmmss PM"

 

hh:mm

A, or P

12-hour clock

"DD-MMM-YYYY hh:mm A"

hhmm

 
 

hh

Examples

Example Return value

DATETIME(`20141231 235959`)

20141231 235959

Returned as a character string, using the current ACL date and time display formats

DATETIME(`20141231 235959`, "DD-MMM-YYYY hh:mm A")

31-Dec-2014 11:59 P

Returned as a character string, using the specified format

DATETIME( )

The current operating system datetime returned as a character string, using the current ACL date display format and hh:mm:ss format

DATETIME(Receipt_timestamp)

The datetime returned as a character string for each value in the Receipt_timestamp field, using the current ACL date and time display formats

DATETIME(Receipt_timestamp, "DD/MM/YYYY hh:mm:ss")

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

Related reference
DATE( ) function
TIME( ) function


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