DTN

Date/Time Numeric

This numeric function is used to convert the date and time in string format as specified by a mask to SQL numeric format.

Syntax

DTN (string-value [,date-mask] [,ERR=line-ref|,ERC=error-code])

string-value: is a string that contains a date in the format specified by date-mask or a null string value for current date.
date-mask: is any string containing a date format using the characters specified below. The default date-mask is "DD-MON-YYYY HH:MI:SS".
line-ref: is the program line number or label to branch to if this directive produces an error.
error-code: is a programmer-defined error code. Valid values are positive or negative whole numbers.

Remarks

The range of valid dates is 01-JAN-9999 (9999BC) through 31-DEC-9999 (9999AD).

If a month is specified that is not one of the standard 12, an ERR=26 results. This is normally caused by an improper match of date-mask to actual date in string-value.

If a day is specified that is not a valid number in the given month, in that given year, an ERR=41 results.

If both a month and a Julian day are specified in the date-mask, Thoroughbred Basic will return ERR=26.

Valid masking characters are:

YY: Two-digit year; century is assumed based on string-value as compared to the years from today's YY-50 through today's YY+49.
YYY: Three-digit year; assumes leading +0.
YYYY: Full four-digit year; optional leading minus sign for BC dates.
MM: Two-digit month (e.g., January = 01, December = 12)
MON: Uppercase, three-character abbreviation of the month (e.g., "JAN").
Mon: Upper/lowercase, three-character abbreviation of the month (e.g., "Jan").
MONTH: Full uppercase name of the month (e.g., "JANUARY").
Month: Upper/lowercase, full name of the month (e.g., "January").
DD: Two-digit day of the month.
DDD: Three-digit day of the year (Julian) from 1 through 366.
DY: Uppercase three-character abbreviation of the day of the week (e.g., "MON").
Dy: Upper/lowercase three-character abbreviation of the day of the week (e.g., "Mon").
DAY: Uppercase day of the week, for example, "MONDAY".
Day: Upper/lowercase day of the week, for example, "Monday".
HH: Hour of the day in 24-hour format of the time given.
MI: Minutes of the time given.
SS: Seconds of the time given.
SS.SSSSSS: Seconds of the time given in maximum of 6-decimal place accuracy.
AM or PM: Returns "AM" for clock times between midnight and noon;"PM" for clock times between noon and midnight.

Specifications for a year, a month, and a day are required to calculate SQL dates. A value of 1 is used for any missing specification. For example:

DTN(“9906”,“YYMM”) is equivalent to DTN(“990601”,“YYMMDD”) DTN(“9906”,“YYDD”) is equivalent to DTN(“990106”,“YYMMDD”) DTN(“0601”,“MMDD”) is equivalent to DTN(“00010601”,“YYYYMMDD”)

Examples

Suppose your program received a date in the string variable DATE_STRING$ as follows, and you wanted to change that format easily:

“Tuesday, June 13, 1989 at 12:00:00”

The simplest way is to convert it to its SQL numeric format and then convert it back out using the NTD Function. The first part looks like:

LET SQL_NUM_DATE = DTN (DATE_STRING$, "Day, Month DD, YYYY at
HH:MI:SS")

This makes SQL_NUM_DATE contain the value 726268.5 which could then be reformatted by:

LET NEW_FORMAT_STRING$ = NTD (SQL_NUM_DATE, "MM/DD/YY")

which yields “06/13/89”. Note that the purpose of date-mask is to tell DTN how to interpret the string-value.

NTD (DTN (DATE_STRING$, "Day, Month DD, YYYY at HH:MI:SS") )

accomplishes the above result in a single expression.

To illustrate how to use a missing specification:

PRINT NTD(DTN("9906","YYMM"),"")

prints 01-JUN-1999 00:00:00 because the value 1 is assumed for the missing day specification.

When a month and a Julian day are specified in the date-mask, as in:

PRINT NTD(DTN("991231","YYMMDDD"),"")

Thoroughbred Basic returns ERR=26.

DTN

Syntax

Remarks

Examples

See also