CTOD()

CTOD() converts a character or numeric expression into a valid datetime expression.

Use CTOD() to create a datetime value from readable character or numeric data. This is often useful for the purposes of comparison with an existing datetime field. For example, to identify all dates before a certain cut-off date (stored in a character field CUTOFF_DATE), you could use the test:

DATE < CTOD(CUTOFF_DATE,“yyyymmdd”)

This usage has largely been replaced by the use of date constants (literals). For more information, see Date, Time and Elapsed Time Constants (Literals).

Function Format

CTOD(C|N <,C>)

The CTOD() function (short for “Character To Date”) converts character strings or numeric values into valid DateTimes.

CTOD() is the standard function name used by a variety of other software. However, unlike other software packages, Analyzer also converts numeric expressions to datetimes.

If the first parameter is a number, it is automatically converted to a character string before conversion.

By default, the first parameter is assumed to be stored as:

Note: To indicate a 12 hour clock, the time format should include either a “p” or a “pm”.

If the first parameter is not stored in the default format, then Analyzer requires that you specify an optional datetime format string. For example, to convert a numeric Julian date of the form “YYDDD”, specify:

CTOD(JULIAN_DATE,“YYDDD”)

Note: The format string uses the same form as used for specifying input or output datetime formats for datetime fields. Punctuation can be included in the format string to be converted. Because the format string is actually a character string, it should be enclosed in matching double or single quotes, not reverse quotes.

Examples

All of the following expressions return the value of December 31, 2005:

CTOD("20051231")

CTOD(20051231)

CTOD(311205,"DDMMYY")

CTOD("311205","DDMMYY")

CTOD(05365,"YYDDD")

CTOD("05365","YYDDD")

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

Assuming a date display format of MM/DD/YYYY:

CTOD("20051220") = ‘12/20/2005‘

CTOD("20DEC05","DDMMMYY") = ‘12/20/2005‘

Note: Correct interpretation of two digit years is solely dependant on the Start of Century setting in your Date Options. For more information, see Start of Century Date.

Assuming a datetime stored in the format “MM/DD/YY HHMMSS” (as a 24 hour clock) with a date display format of YYYY/MM/DD:

CTOD("12/31/11 162235","MM/DD/YY HHMMSS") = ‘2011/12/31 16:22:35‘

Assuming a datetime stored in the format “MM/DD/YY HHMMSS PM” (as a 12 hour clock) with a date display format of YYYY/MM/DD:

CTOD("12/31/11 102235","MM/DD/YY HHMMSS PM") = ‘2011/12/31 10:22:35‘

Assuming a time value stored in the format “HHMMSS”:

CTOD("102235","HHMMSS") = ‘10:22:35‘

Assuming you have an accounts receivable transaction file that includes a date field named DueDate, you can display all transactions with a due date before July 1, 2005.

To do this you need to compare the DueDate field to the value of July 1, 2005. Because the DueDate field is interpreted by Analyzer as a date field, you cannot simply compare it to 07/01/2005 or 20050701. You must convert July 1, 2005, into a date value.

For example, use an IF test or filter as follows:

DueDate < CTOD("07/01/2005","MM/DD/YYYY")

Alternatively, you can specify a date literal in the format YYYYMMDD to obtain the same result:

DueDate < `20050701`