CTODT( ) function

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

Syntax

CTODT(string/number <,format>)

Parameters

Name Type Description
string/number

character

numeric

 The field, expression, or literal value to convert to a datetime.
format

optional

 character

The date format of string/number. The format is required for values that use any date format other than YYYYMMDD or YYMMDD for the date portion of the value, for example "DD/MM/YYYY".

Output

Datetime. The datetime value is output using the current Analytics date and time display formats.

Examples

Basic examples

Character literal input

Returns `20141231t235959` displayed as 31 Dec 2014 23:59:59 assuming a current Analytics date and time display formats of DD MMM YYYY and hh:mm:ss:

CTODT("20141231 235959")
CTODT("31/12/2014 23:59:59", "DD/MM/YYYY hh:mm:ss")

Numeric literal input

Returns `20141231t235959` displayed as 31 Dec 2014 23:59:59 assuming a current Analytics date and time display formats of DD MMM YYYY and hh:mm:ss:

CTODT(20141231.235959)
CTODT(31122014.235959, "DDMMYYYY.hhmmss")

Character field input

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

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

Numeric field input

Returns each value in the Payment_timestamp numeric field as a datetime, using the current Analytics date display format:

CTODT(Payment_timestamp, "DD/MM/YYYY hh:mm:ss")

Advanced examples

Compare a character or numeric field to a datetime

Use the CTODT( ) function to compare a datetime against character or numeric fields that contain values representing datetimes.

The filter below compares two values:

  • the character Receipt_timestamp field that stores datetimes as character data in the format DD/MM/YYYY hh:mm:ss
  • the literal datetime value July 1, 2014 13:30:00
SET FILTER TO CTODT(Receipt_timestamp, "DD/MM/YYYY hh:mm:ss") < `20140701t133000`

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:

Required datetime formats

Character and numeric fields containing datetime values must match the formats in the table below. The datetime values can use any combination of the date, separator, and time formats valid for their data type. The date must precede the time, and there must be a separator between the two.

The date portion of values can use any date format supported by Analytics, and valid for the data type, as long as formats other than YYYYMMDD or YYMMDD are correctly defined by format. If you use format, you must also specify the time format, which must be one of the time formats that appear in the table below.

Analytics automatically recognizes the separator between the date and time portions of datetime values, so there is no need to specify the separator in format. You can specify the separator if you want to.

Date formats

Separator formats

Time formats

Character fields

YYYYMMDD

single blank space

hhmmss

hh:mm:ss

YYMMDD

the letter 't'

hhmm

hh:mm

any Analytics-supported date format, valid for the data type, if defined by format

the letter 'T'

hh

 

  

+/-hhmm

+/-hh:mm

(UTC offset)

 

 

+/-hh

(UTC offset)

 

 

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.

Numeric fields

YYYYMMDD

decimal point

hhmmss

YYMMDD

 

hhmm

any Analytics-supported date format, valid for the data type, if defined by format

 

hh

Other datetime conversion functions

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

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

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.

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.

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