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

string/number

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

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 date format other than YYYYMMDD or YYMMDD. The format parameter must be enclosed in quotation marks.

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 ACL date display format.

Remarks

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 ACL, and valid for the data type, as long as formats other than YYYYMMDD or YYMMDD are correctly defined by the format parameter.

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

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

CTOD("20141231")

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

CTOD("20141231 235959")

31 Dec 2014

A character literal returned as a date, assuming a current ACL date display format of DD MMM YYYY.

CTOD(20141231)

CTOD(31122014, "DDMMYYYY")

CTOD(20141231.235959)

31 Dec 2014

A numeric literal returned as a date, assuming a current ACL date display format of DD MMM YYYY.

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

CTOD(Receipt_timestamp)

Each value in the specified character field returned as a date, using the current ACL date display format.

CTOD(Due_date, "DDMMYYYY")

CTOD(Payment_timestamp)

Each value in the specified numeric field returned as a date, using the current ACL date display format.

Compare a character or numeric field to a date

You can use the CTOD( ) function to compare a character or numeric field, containing values representing dates, to a date. The filter below compares values in the Due_date field, which stores dates as numbers, and uses the format DDMMYYYY, to July 1, 2014. The date literal in this expression must be specified using backquotes.

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

Related reference
CTODT( ) function
CTOT( ) function


(C) 2015 ACL Services Ltd. All Rights Reserved.