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

string/number

Character or 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 ACL time display format.

Remarks

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

Examples

Example Return value

CTOT("t235959")

CTOT("23:59:59")

CTOT("20141231 235959")

23:59:59

A character literal returned as a time, assuming a current ACL time display format of hh:mm:ss.

CTOT(.235959)

CTOT(0.235959)

CTOT(20141231.235959)

23:59:59

A numeric literal returned as a time, assuming a current ACL time display format of hh:mm:ss.

CTOT(Login_time)

Each value in the Login_time character or numeric field returned as a time, using the current ACL time display format.

CTOT(Receipt_timestamp)

Each value in the Receipt_timestamp character or numeric field returned as a time without the date portion, using the current ACL time display format.

Compare a character or numeric field to a time

You can use the CTOT( ) function to compare a character or numeric field, containing values representing times, to a time. The filter below compares values in the Login_time field, which stores times as numbers, to 09:30:00 AM. The time literal in this expression must be specified using backquotes.

SET FILTER TO CTOT(Login_time) > ‘t093000‘

Related reference
CTOD( ) function
CTODT( ) function


(C) 2013 ACL Services Ltd. All Rights Reserved. | Send feedback