AppLib CONVERT DATE

From Eigenpedia

(Redirected from LucidDbAppLib CONVERT DATE)

1 Syntax
2 Purpose
3 Parameters
- 3.1 Mask Elements [Note: only standard masking works at the moment]
4 Output
5 Example
6 Source Code

Syntax

APPLIB.CONVERT_DATE ( date_string, mask [, reject] )

Purpose

Converts a character representation of a date to the date datatype. The date_string parameter's syntax varies depending on the use of the optional mask parameter.

Parameters

date_string [VARCHAR(128)]:
- If the mask parameter is not specified, the representation of the date in the date_string parameter must be in the standard ODBC format, that is, YYYY-MM-DD. You may have single digits for the month and year. '1998-03-06' and '1998-3-6' are both valid strings that describe the same date.
- If the mask parameter is specified, the date_string parameter consists of
  - One each of the date elements year, month and day in one of the allowable forms.
  - Optional separator and "noise" characters that cannot include any of the letters d, m, y, D, M or Y.

Allowable forms for date_string parameter:
Date element	Allowed
Year	Four digit number = the exact year
	Two digit number 00-49 represents the years 2000-2049 50-99 represents the years 1950-1999
	Note: A two-digit year number is only allowed if you specify a mask
Month	a one or two digit month number
	~~one of the following short month names: JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC~~
	~~one of the following full month names: JANUARY, FEBRUARY, MARCH, APRIL, MAY, JUNE, JULY, AUGUST, SEPTEMBER, OCTOBER, NOVEMBER, DECEMBER~~
	~~Note: the short or full month names are case insensitive.~~
Day	A one or two digit day number

mask [VARCHAR(50)]: The mask parameter describes the format of the date in the parameter date_string and consists of
- One or more occurrences of each of the mask token letters D, M and Y (the mask token letters may be in lower or upper case)
- Optional separator and noise characters - these correspond exactly to the separator and noise characters in the date_string parameter
reject [BOOLEAN]: optional parameter; if true, throws an exception on invalid date otherwise returns null

Mask Elements [Note: only standard masking works at the moment]

The way you represent the mask is different when the data elements in the date_string parameter are delimited compared to when the date elements in the date_string parameter are combined (that is, non-delimited).

Mask Tokens to Represent Delimited Date Elements -- When the date elements are fully delimited in the date string, they can be represented in the mask by the single characters D, M and Y (or for readability, multiple occurrences of the appropriate letters).

~~For example:~~

   The standard mask for the date string 1998/12/25 is Y/M/D.
   The standard mask for the date string JAN 14, 2003 is M D,
   (You could also use the masks YY/MM/DD and YYYY/MM/DD to represent 1998/12/25.)

Mask Tokens to Represent Non-Delimited Date Elements

   When date elements are not delimited, (as in 980416), then the number of characters and digits in the string are significant. In this case, you must show the exact number of digits in the mask for each date element.

   For example:

   The only allowed mask for the date string 980416 is YYMMDD
   For 23JAN-1998, you must specify 2 digits for the day, and three for the month; the year, is delimited (by the preceding -), and so does not require multiple letters in the mask.
   The standard mask for 23JAN-1998 is DDMMM-Y (but could also be DDMMM-YYYY).