The TOSTRING and TODATE Conversion Options provide different formatting options.
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.
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 so, the program performs the conversion, which can take more time than if the data store did the conversion.
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). |