SQL - DATE() Function

Syntax

DATE (num1{, mask})

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. 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 str 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:

Indicator

Meaning

%Y

year

%M

month

%D

day

The above sequences may be optionally followed by another letter requesting a specific format of the information. The following modifiers are supported:

Modifier

Meaning

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. Assume the date to be July 21, 1998.

Format

Default

p

d

z

s

l

%Y

1998

CHR(98)

1998

98

1998

1998

%M

7

CHR(7)

7

07

Jul

July

%D

21

CHR(21)

21

21

Mon

Monday

Notice that the "s" and "l" formats of the day (%D) give the day of the week.



______________________________________________________________________________________

Copyright BASIS International Ltd. BBj®, Visual PRO/5®, PRO/5®, and BBx® are registered trademarks.