DATETIME( ) function

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

Syntax

DATETIME(<datetime> <,format>)

Parameters

Name Type Description
datetime

optional

datetime

The field, expression, or literal value to convert. 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

Literal datetime input

Returns "20141231 235959" in the current Analytics date and time display formats:

DATETIME(`20141231 235959`)

Returns "31-Dec-2014 11:59 P":

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

Returns the current operating system date and time as a character string, using the current Analytics date and time display formats:

DATETIME()

Field input

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

DATETIME(Receipt_timestamp)

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

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

Remarks

Date and time functions can sometimes be challenging to use correctly. In the Help, function topics describe the specific details of how each function works. For information about some general considerations when using date and time functions, see the following topics:

Output string length

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

Parameter details

A field specified for datetime can use any 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 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 Analytics automatically uses a single space as a separator in the output string.
  • The AM/PM format is optional, and is placed last.
  • format 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 Analytics 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

 

 

Specifying a literal datetime value

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

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

     

Other datetime conversion functions

Datetime to Character conversion

Function Description

DATE( )

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

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".