CTOT( ) function

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

Syntax

CTOT(string/number)

Parameters

Name	Type	Description
string/number	character numeric	The field, expression, or literal value to convert to a time, or from which to extract the time.

Name

Type

Description

string/number

character

numeric

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

Output

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

Examples

Basic examples

Character literal input

Returns `t235959` displayed as 23:59:59 assuming a current Analytics time display format of hh:mm:ss:

CTOT("t235959")

CTOT("23:59:59")

CTOT("20141231 235959")

Numeric literal input

Returns `t235959` displayed as 23:59:59 assuming a current Analytics time display format of hh:mm:ss:

CTOT(.235959)

CTOT(0.235959)

CTOT(20141231.235959)

Character field input

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

CTOT(Login_time)

Numeric field input

Returns each value in the Payment_datetime numeric field as a time, without any date portion, using the current Analytics time display format:

CTOT(Payment_datetime)

Advanced examples

Compare a character or numeric field to a time

Use the CTOT( ) function to compare a time against character or numeric fields that contain values representing times.

The filter below compares two values:

the numeric Login_time field that stores times as numeric data
the literal time value 09:30:00

SET FILTER TO CTOT(Login_time) > `t093000`

Remarks

Required datetime formats

Character and numeric fields containing time or datetime values must match the formats in the table below.

Time values can use any combination of separator and time format. There must be a separator before the time value, or colons between the time components, for the function to operate correctly.

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.

Use the CTOD( ) function if you want to convert a character or numeric date value to a date, or extract the date from a character or numeric datetime value and return it as a date.

Use the CTODT( ) function if you want to convert a character or numeric datetime value to a datetime.

Date formats	Separator formats	Time formats
Character fields
YYYYMMDD	single blank space	hhmmss hh:mm:ss
YYMMDD	the letter 't'	hhmm hh:mm
	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
		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".
CTODT( )	Converts a character or numeric datetime value to a datetime. Abbreviation for "Character to Datetime".

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