TO_CHAR
The TO_CHAR function converts a date value stored in your database into a character string with a specific format.
The basic syntax of the TO_CHAR function is as follows:
TO_CHAR(d, format_model, nls_params);
For a date or date interval 'd', 'format_model' and 'nls_parms' tailor string formats with the components listed below:
- AD / A.D. / BC / B.C.: Anno Domini or Before Christ indicator, with or without periods.
- AM / A.M. / PM / P.M.: Morning or afternoon hours, with or without periods.
- CC / SCC: Century.
- D: Day of the week, 1 through 7.
- DAY: The name of the day spelled out.
- DD: Day of the month, 1 thorough 31.
- DDD: Day of the year, 1 through 366.
- DL: Long date format. The appearance is determined by the NLS_DATE_LANGUAGE parameter. For example, with NLS_DATE_LANGUAGE = AMERICA for DL format, output would be like "Monday, January 08, 2024."
- DS: Short date format. Appearance is determined by the NLS_DATE_LANGUAGE parameter. For example, with NLS_DATE_LANGUAGE = AMERICA for DS format, output would be like "1/8/2024."
- FF: Fractional seconds.
- FM: Used in combination with other elements to direct the suppression of leading or trailing blanks.
- FX: Exact matching between the character data and the format model.
- HH / HH12: Hour of the day, 1 through 12 (both are identical). 12 midnight is represented as 12.
- HH24: Hour of the day, 1 through 24. 12 midnight is represented by 00.
- IW: Week of the year 1 through 53.
- WW: Week of the year, 1 through 53. Week 1 starts on the first day of the month and ends on the seventh day of the month.
- W: The week of the month, 1 through 5. Week 1 starts on the first day of the month and ends on the seventh day of the month.
- MI: Minute. 0 through 59.
- SS: Seconds, 0 through 59.
- SSSS: Seconds past midnight 0 through 86399.
- MM: Month in double digits, 01 through 12.
- MON: Abbreviated name of month, such as JAN, FEB, MAR.
- MONTH: Name of month spelled out.
- Q: Quarter of year.
- RR: Two-digit year from 1950 to 2049. For example, RR would interpret 22 as 2022 and 78 as 1978.
- RRRR: Four-digit year.
- YY: Two-digit year with no sliding window. For example, YY would interpret 22 as 2022 and 78 as 2078.
- YYYY: Four-digit year.
- TS: The short time format.
A frequent error in the format model is mistakenly using MM when the intention is to represent minutes. For example:
In the figure shown above, I mistakenly used format model DL HH24:MM:SS. While my intention is to represent the current minute, MM returns the current month (01, January) in the output! The correct format model for the purpose is DL HH24:MI:SS, not MM!
Another common error is the potential confusion between RR and YY. In Oracle SQL, the RR represents the two-digit years from 1950 to 2049, whereas YY is 21st century. For example:
TO_DATE(c, format_model, nls_parms)
In Oracle SQL, TO_DATE converts a character string to a date value. The basic syntax for the TO_DATE function is as follows:
TO_DATE(c, format_model, nls_params);
The character string 'c' is the value on which you want to apply type conversion, 'format_model' is an optional parameter, structuring the interpretation of the character string. It identifies the date information according to the listed earlier. Additionally, nls_parms specifies the national language support parameters for your requirements. The TO_DATE function is particularly useful when you want to convert a non-standard date representations stored as a character string to a system default date format. For example:
In practice, to leverage their formatting capabilities, we often nest conversion functions. For example, to determine the weekday of a particular date, you can nest a TO_DATE within a TO_CHAR function as follows:
0 Comments