Conversion Options
Options
Aggregate Options
Date Formatting Options
The TOSTRING and TODATE Conversion Options provide different
formatting options.
TOSTRING Formatting
TOSTRING takes a date value and creates a representation
of it as a string. The optional format specification parameter defines
the structure of the string to create. For example, if the date
information is 1998-APR-02, you can format the resulting string
as April 2, 1998.
You can use any combination in your format except
those that return the number of a day or week within a year for
a given date. For example, TOSTRING (1998-APR-02, ‘MONTH DD, YY’)
returns the value APRIL 02, 98.
If you use a relational database management
system, your data store may not be able to use its native (built-in)
functions to execute the request. If this is the case, the conversion
is handled by the program, which might take more time than if the
data store did the conversion.
TODATE Formatting Options
TODATE takes a string value representing a date
or time and converts it to a date object. The optional format specification
parameter defines the format used to represent the date in the string.
For example, for a string containing the date April 2, 1998 ,the
format specification should contain Month DD, YYYY. The following
table outlines the formatting options available:
| Abbreviation |
Description |
| YY |
Defines the year as a two-digit number, for example,
07. |
| YYYY |
Defines the year as a four-digit number, for example,
2007. |
| MONTH |
Defines the month using its name in uppercase letters,
for example, APRIL. |
| month |
Defines the month using its name in lowercase letters,
for example, april. |
| Month |
Defines the month using its name with an initial capital
letter, for example, April. |
| MON |
Defines the month using its three-letter abbreviation
in uppercase, for example, APR. |
| mon |
Defines the month using its three-letter abbreviation
in lowercase, for example, apr. |
| MM |
Defines the month using its two-number abbreviation,
for example, 04. |
| DAY |
Defines the day using its name in uppercase letters,
for example, FRIDAY. |
| day |
Defines the day using its name in lowercase letters,
for example, friday. |
| Day |
Defines the day using its name with an initial capital
letter, for example, Friday. |
| DY |
Defines the day using its abbreviation in uppercase,
for example, FRI. |
| dy |
Defines the day using its abbreviation in lowercase,
for example, fri. |
| DD |
Defines the day using its two-number abbreviation, for
example, 06. |
| hh24 |
Defines an hour using its number in the range [0-24]. |
| hh12 |
Defines an hour using its number in the range [0-12]. |
| hh |
Defines an hour using its number in the default representation
(by default, hh24). |
| mm |
Defines minutes. |
| ss |
Defines seconds. |
| ms |
Defines milliseconds. |
| am|pm |
Uses the ante-meridiem (morning) and post-meridiem (after
noon) specification. Noon is often called 12:00 p.m. and midnight
12:00 a.m., as at the beginning of a day. This format is considered only when used with
the time range [1-12] (format hh12). |