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

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

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

[ Back to top ]

ACL Scripting Guide 14.1