SQL — DATE() Function
Syntax
DATE(num1{,num2}{:mask}{,ERR=lineref})
Description
The DATE() function takes a Julian date and optional time-of-day and formats it into a more familiar calendar date and time string.
|
Parameter |
Description |
|---|---|
|
num1 |
Julian date. |
|
num2 |
Time of day in SETTIME format (hours and fractional hours). If this parameter is not included, the current time is used. |
|
mask |
Output format of the result. If this parameter is not included, a "MM/DD/YY" type of date is produced. |
|
ERR=lineref |
Branch to be taken if an error occurs during execution. |
Num1 is a Julian date and must always be present. If zero is used, it defaults to today's date. If the date is not present, then the current time is used. If the current time is not present, then a "MM/DD/YY" type of date is produced. The following is the simplest form and will print the current date:
Select DATE(Dcol) from customer
The contents of mask determines the output format. The BASIS DBMS will scan mask and insert various parts of the date into the text as specified by the mask. For example, if "%Y" appears anywhere in the mask, it will be replaced by the year:
Select DATE(ship_date, 'The ship year is %Y') from order_header
The above example outputs "The ship year is 1998". Several format indicators are recognized. These indicators all begin with a "%", followed by a letter indicating which component of the date to insert. In the above example, "%Y" accesses the year. The following components may be accessed:
|
Format |
Description |
|---|---|
|
%Y |
Year |
|
%M |
Month |
|
%D |
Day |
|
%H |
Hour (24-hour clock) |
|
%h |
Hour (12-hour clock) |
|
%m |
Minute |
|
%s |
Second |
|
%p |
AM/PM |
The above sequences may be optionally followed by another letter requesting a specific format of the information. The following modifiers are supported:
|
Modifier |
Description |
|---|---|
|
z |
Zero-fill |
|
s |
Short text |
|
l |
Long text |
|
p |
Packed number (in CHR() form) |
|
d |
Decimal (default format) |
As an example, the following will print a date in "MM/DD/YY" format (requiring the zero-fill operation):
DATE(0,'%Mz/%Dz/%Yz')
Below is a table with examples of all the combinations for the date July 11, 1999 at 6:30 PM:
|
Format |
Default |
Packed Number |
Decimal |
Zero-fill |
Short Text |
Long Text |
|---|---|---|---|---|---|---|
|
%Y |
1999 |
CHR(99) |
1999 |
99 |
1999 |
1999 |
|
%M |
7 |
CHR(7) |
7 |
07 |
Jul |
July |
|
%D |
11 |
CHR(11) |
11 |
11 |
Sun |
Sunday |
|
%H |
18 |
CHR(18) |
18 |
18 |
18 |
18 |
|
%h |
6 |
CHR(6) |
6 |
06 |
6 |
6 |
|
%m |
30 |
CHR(30) |
30 |
30 |
30 |
30 |
|
%s |
0 |
CHR(0) |
0 |
00 |
0 |
0 |
|
%p |
PM |
(N/A) |
(N/A) |
(N/A) |
(N/A) |
(N/A) |
Notice that the "s" and "l" formats of the day (%D) give the day of the week.