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

Name Type Description
date/datetime

optional

datetime

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

optional

 character

The format to apply to the output string, for example "DD/MM/YYYY". If omitted, the current Analytics date display format is used. You cannot specify a format if you have omitted date/datetime.

Output

Character.

Examples

Basic examples

Returns "20141231" in the current Analytics date display format:

DATE(`20141231 235959`)

Returns "31-Dec-2014":

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

Returns the current operating system date as a character string, using the current Analytics date display format:

DATE()

Returns each value in the Receipt_timestamp field as a character string using the current Analytics date display format:

DATE(Receipt_timestamp)

Returns each value in the Receipt_timestamp field as a character string using the specified date display format:

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

Remarks

Output string length

The length of the output string is always 12 characters. If the specified output format, or the Analytics date display format, is less than 12 characters, the output string is padded with trailing blank spaces.

Parameter details

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

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

  • DD/MM/YYYY

  • MM-DD-YY

  • DD MMM YYYY

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

Specifying a literal date or datetime value

When specifying a literal date or datetime value for date/datetime, you are restricted to the formats in the table below, and you must enclose the value in backquotes – for example, `20141231`.

Do not use any separators such as slashes (/) or colons (:) between the individual components of dates or times.

  • Datetime values – you can use any combination of the date, separator, and time formats listed in the table below. The date must precede the time, and you must use a separator between the two. Valid separators are a single blank space, the letter 't', or the letter 'T'.

  • Time values – you must specify times using the 24-hour clock. Offsets from Coordinated Universal Time (UTC) must be prefaced by a plus sign (+) or a minus sign (-).

    Example formats

    Example literal values

    YYYYMMDD

    `20141231`

    YYMMDD

    `141231`

    YYYYMMDD hhmmss

    `20141231 235959`

    YYMMDDthhmm

    `141231t2359`

    YYYYMMDDThh

    `20141231T23`

    YYYYMMDD hhmmss+/-hhmm

    (UTC offset)

    `20141231 235959-0500`

    YYMMDD hhmm+/-hh

    (UTC offset)

    `141231 2359+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.

     

Related functions

If you need to return the current operating system date as a datetime value, use TODAY( ) instead of DATE( ).

Other datetime conversion functions

Datetime to Character conversion

Function Description

DATETIME( )

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

TIME( )

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

Character or Numeric to Datetime conversion

Function Description

CTOD( )

Converts a character or numeric date value to a date. Can also extract the date from a character or numeric datetime value and return it as a date. Abbreviation for "Character to Date".

CTODT( )

Converts a character or numeric datetime value to a datetime. Abbreviation for "Character to Datetime".

CTOT( )

Converts a character or numeric time value to a time. Can also extract the time from a character or numeric datetime value and return it as a time. Abbreviation for "Character to Time".

Serial to Datetime conversion

Function Description

STOD( )

Converts a serial date – that is, a date expressed as an integer – to a date value. Abbreviation for "Serial to Date".

STODT( )

Converts a serial datetime – that is, a datetime expressed as an integer, and a fractional portion of 24 hours – to a datetime value. Abbreviation for "Serial to Datetime".

STOT( )

Converts a serial time – that is, a time expressed as a fractional portion of 24 hours, with 24 hours equaling 1 – to a time value. Abbreviation for "Serial to Time".