CTOD( ) function

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

Syntax

CTOD(string/number <,format>)

Parameters

Name	Type	Description
string/number	character numeric	The field, expression, or literal value to convert to a date, or from which to extract the date.
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 example "DD/MM/YYYY". Note If you use the CTOD function with a datetime value that requires the format parameter, specify only the date portion of the format, and not the time portion. For example: CTOD("31/12/2014 23:59:59", "DD/MM/YYYY") Specifying the time portion prevents the results from appearing.

Name

Type

Description

string/number

character

numeric

The field, expression, or literal value to convert to a date, or from which to extract the date.

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 example "DD/MM/YYYY".

Note

If you use the CTOD function with a datetime value that requires the format parameter, specify only the date portion of the format, and not the time portion. For example:

 CTOD("31/12/2014 23:59:59", "DD/MM/YYYY")

Specifying the time portion prevents the results from appearing.

Output

Datetime. The date value is output using the current Analytics date display format.

Examples

Basic examples

Character literal input

Returns `20141231` displayed as 31 Dec 2014 assuming a current Analytics date display format of DD MMM YYYY:

CTOD("20141231")

CTOD("31/12/2014", "DD/MM/YYYY")

CTOD("20141231 235959")

Numeric literal input

Returns `20141231` displayed as 31 Dec 2014 assuming a current Analytics date display format of DD MMM YYYY:

CTOD(20141231)

CTOD(31122014, "DDMMYYYY")

CTOD(20141231.235959)

Character field input

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

CTOD(Invoice_date, "DD/MM/YYYY")

CTOD(Receipt_timestamp)

Numeric field input

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

CTOD(Due_date, "DDMMYYYY")

CTOD(Payment_timestamp)

Advanced examples

Compare a character or numeric field to a date

Use the CTOD( ) function to compare a date against character or numeric fields that contain values representing dates.

The filter below compares two values:

the numeric Due_date field that stores dates as numbers in the format DDMMYYYY
the literal date value July 1, 2014

SET FILTER TO CTOD(Due_date, "DDMMYYYY") < `20140701`

Remarks

Required date formats

Character and numeric fields containing date or datetime values must match the formats in the table below. 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.

Dates, or the date portion of datetime 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.

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

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