LucidDbAppLib CHAR TO DATE

From Eigenpedia

1 Syntax
2 Purpose
3 Parameters
- 3.1 Format string syntax
4 Example
5 Untried/Untested functionality
6 Source Code

Syntax

APPLIB.CHAR_TO_DATE ( format, dateString )

Purpose

Converts a string to a date, according to the specified format string.

Parameters

format: the date formatting which is used by the given date string. [VARCHAR]
date: the date to be formatted [DATE]

Format string syntax

A format string is a combination of date/time patterns. Only certain combinations of time patterns are supported. The time patterns are case-sensitive sequences of letters which represent a time field.

format strings which contain year, month, and the day of month
- examples are yyyy-M-d or MMddyyyy
- this will return the date on the specified day, month, and year
format strings which contain month in year and year
- examples are yyyy-M or MMyyyy
- this will return the date of the first day of the specified month and year
format strings which contain week number in year and year
- examples yyyy-w or w/yyyy
- this will return the 1st day of the specified week and year. This will always be a Sunday unless it is the first week of a year.
- The first week in a year always begins on the first day of the year. This is typically Jan 1st. This means that the first week of a year may not be a complete 7 days. The last week in a year might also not contain 7 days. This means that a year can have 53 or 54 weeks.
- Note that this behavior is different from the Java Calendar behavior in that weeks do not have to be a 7 day period which begins on Sunday.

**Time Pattern table**
Field	Form/Pattern	Notes
Year	yyyy (4 digits)
Month	M (1 or 2 digits)	the number can contain 1 or 2 digits (both 02 and 2 will work), unless this pattern is adjacent to other fields. In that case, it will only pick up one digit.
Month	MM (2 digits)	the number should contain 2 digits. This form should be used if the pattern has other fields adjacent to the month field.
Day of month	d (1 or 2 digits)	the number can contain 1 or 2 digits (both 02 and 2 will work), unless this pattern is adjacent to other fields. In that case it will only pick up one digit.
Day of month	dd (2 digits)	the number should contain 2 digits. This form should be used if the pattern has other fields adjacent to the day of month field.
Week in year	w

Example

VALUES( APPLIB.CHAR_TO_DATE('MMM-F-EE', 'MAR-2-MON'), 
    APPLIB.CHAR_TO_DATE('M/d/yyyy', '2/12/2007'), 
    APPLIB.CHAR_TO_DATE('w-yyyy', '50-1980'),
    APPLIB.CHAR_TO_DATE('M-yyyy', '10-2007') )

RETURNS:

SQL	RESULT
VALUES( APPLIB.CHAR_TO_DATE('M/d/yyyy', '2/12/2007'))	2007-02-12
VALUES( APPLIB.CHAR_TO_DATE('w-yyyy', '50-1980'))	1980-12-07
VALUES( APPLIB.CHAR_TO_DATE('M-yyyy', '10-2007'))	2007-10-01
VALUES( APPLIB.CHAR_TO_DATE('yyyyMMdd', '19890209'))	1989-02-09
VALUES( APPLIB.CHAR_TO_DATE('yyyyMdd', '1989209'))	1989-02-09

Untried/Untested functionality

The functionality listed under this section has not been fully tested and is not considered supported at this time. The expected behavior for the usage patterns below have not been well-specified and they should be used at your own risk.

Any format strings which use combinations of date/time patterns other than the ones listed above in the Format String Syntax section.
Additional date/time patterns in the table below.

The following fields in the table below can be used in a format string. Any fields not specified will default to the fields in the date Jan 1, 1970. Exception will be thrown if incompatible values for different fields are specified.

NOTE:' format string is case sensitive

Field	Form	Notes
Year	yy (2 digits)	Century is calculated as within 80 years before and 20 years after the current year
Month	MMM	Abbreviated month name (ex. Jan, Feb, MAR, DEC)
Month	MMMM	Full month name (ex. January, MARCH, may)
Week in Month	W
Day of week	EEEE or EE
Day of week in month	F (e.g., 3rd Thursday)
Day in year	DDD (3 digits)	D (1, 2, or 3 digits)
Era (BC/AD)	G