CTODT( ) function

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

Syntax

CTODT(string/number <,format>)

Parameters

string/number

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

format

Optional. Character. The format of the character or numeric values referenced by the string/number parameter. The format parameter is required for values that use any format other than YYYYMMDD or YYMMDD for the date portion of the value. The format parameter must be enclosed in quotation marks.

Output

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

Remarks

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 ACL, and valid for the data type, as long as formats other than YYYYMMDD or YYMMDD are correctly defined by the format parameter. If you use the format parameter you must also specify the time format, which must be one of the time formats that appear in the table below.

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

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 CTOT( ) function if you want to convert a character or numeric time value to a time, or extract the time from a character or numeric datetime value and return it as a time.

Date formats

Separator formats

Time formats

Character fields

YYYYMMDD

single blank space

hhmmss

hh:mm:ss

YYMMDD

the letter ‘t’

hhmm

hh:mm

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

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 ACL-supported date format, valid for the data type, if defined by the format parameter

hh

Examples

Example Return value

CTODT("20141231 235959")

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

31 Dec 2014 23:59:59

A character literal returned as a datetime, assuming current ACL date and time display formats of DD MMM YYYY and hh:mm:ss.

CTODT(20141231.235959)

CTODT(31122014.235959, "DDMMYYYY.hhmmss")

31 Dec 2014 23:59:59

A numeric literal returned as a datetime, assuming current ACL date and time display formats of DD MMM YYYY and hh:mm:ss.

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

Each value in the Receipt_timestamp character field returned as a datetime, using the current ACL date and time display formats.

CTODT(Payment_timestamp)

Each value in the Payment_timestamp numeric field returned as a datetime, using the current ACL date and time display formats.

Compare a character or numeric field to a datetime

You can use the CTODT( ) function to compare a character or numeric field, containing values representing datetimes, to a datetime. The filter below compares values in the Receipt_timestamp field, which stores datetimes as character data, and uses the format DD/MM/YYYY hh:mm:ss, to July 1, 2014 13:30:00. The datetime literal in this expression must be specified using backquotes.

SET FILTER TO CTODT(Receipt_timestamp, "DD/MM/YYYY hh:mm:ss") < ‘20140701t133000‘

Related reference
CTOD( ) function
CTOT( ) function


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