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. 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). |