2.1 How do I…Change the format of date fields returned in SQL*Plus?
Problem
I need to format date and time values. The default representation of the date columns’ type is not acceptable for my applications and reports. My applications need date and time values formatted in a variety of ways. How do I change the format of date and time values returned in SQL*Plus?
Technique
The TO_CHAR function converts a date value to character representations. You can use it within SQL statements or PL/SQL modules to present date values in more useful formats. The TO_CHAR function is overloaded in Oracle and has different uses, depending on the parameter list passed to the function. The syntax of the TO_CHAR function used to format date values is shown here:
TO_CHAR(date_value, ‘format mask’)
The NEW_TIME function displays the time portion of a date field relative to any two time zones. This is useful when displaying times in a zone other than where the data is stored. The syntax of the NEW_TIME function used to convert time among time zones is shown here:
NEW_TIME(date,zone_input,zone_output)
With the TO_CHAR function, the format mask specifies the format of the date when it is returned by the function. You can specify the output format any way you want by using valid date format models. Table 2.1 shows the valid date format models.
Table 2.1 Date format models Format Description MM The number of the month (1–12). RM The month specified as a Roman numeral. MON The three-letter abbreviation of the month. MONTH The month fully spelled out. D The number of the day in the week. DD The number of the day in the month. DDD The number of the day in the year. DY The three-letter abbreviation of the day. DAY The day fully spelled out. Y The last digit of the year. I The last digit of the year, based on ISO standards. YY The last two digits of the year. IY The last two digits of the year, based on ISO standards. YYY The last three digits of the year. IYY The last three digits of the year, based on ISO standards. YYYY The full four-digit year. IYYY The four-digit year, based on ISO standards. SYYYY The year in a signed format, BC values represented negative. Y,YYY The year with a comma in the second position. SCC or CC The century; S prefixes BC dates with a -. RR The last two digits of the year used for year 2000 issues. YEAR The year spelled out. SYEAR The year spelled out; S prefixes BC dates with a -. Q The number of the quarter. WW The number of the week in the year. W The number of the week in the month. IW The week of the year, based on ISO standards (1–53). J The number of days since December 31, 4713 B.C. HH or H12 The hour of the day (1–12). HH24 The hour of the day (1–24). MI The minutes of the hour. SS The seconds of the minute. Format Description SSSSS The seconds since midnight. A.M. A.M. or P.M. is displayed, depending on the time of day. P.M. A.M. or P.M. is displayed, depending on the time of day. AM AM or PM is displayed, depending on the time of day. PM AM or PM is displayed, depending on the time of day. A.D. B.C. or A.D. is displayed, depending on the date. B.C. (Same as A.D.) AD BC or AD is displayed, depending on the date. BC (Same as AD.) fm The prefix of MONTH or DAY to suppress padded spaces. th The suffix of the number format to cause th, st, or rd to be added to the end of the number. sp The suffix of a number to force the number to be spelled out. spth The suffix of a number to force it to be spelled out and given an ordinal suffix. thsp (Same as spth.) You can use combinations of the format models with punctuation symbols to create an e#normous number of possibilities. In the ISO standards, a week starts on Monday and ends on Sunday. This means that a date can fall on the fifty-third week of the year in ISO standards, depending on whether January 1 is before a Friday.
The NEW_TIME function returns the time in zone_input as if it were in zone_output. For example, the time can be specified as Central standard, although th#e data is stored in Pacific standard time. The zone_input and zone_output can be specified any way you want by using valid time zone format models. Table 2.2 shows the valid time format models.
Table 2.2 Time format models Format Description AST/ADT Atlantic (standard/daylight) time. BST/BDT Bering time. CST/CDT Central time. EST/EDT Eastern time. GMT Greenwich mean time. HST/HDT Alaska/Hawaii time. MST/MDT Mountain time. NST Newfoundland time. PST/PDT Pacific time. YST/YDT Yukon time. Steps
1. Run SQL*Plus and connect as the WAITE user account. CHP2_1.SQL, shown in Figure 2.1, creates the SAMPLE2 table and inserts a sample record. The SAMPLE2 table will be used to show several of the available date formatting functions.
The SAMPLE2 table is created and populated with a single date value, which is queried in the examples using date formatting functions. The COLUMN commands contained in the script format the columns in the examples to make the output more readable. Run the file to create the sample table and sample record.
SQL> START CHP2_1.sql
Table created.
1 row created.
Commit complete.
2. Load CHP2_2.SQL into the SQL buffer, shown in Figure 2.2. The file contains a query of the sample table that returns a date column in a variety of formats.
Line 2 returns the date column in the default Oracle format. Lines 3 through 6 format the date in a variety of ways. Line 3 demonstrates the two-digit year format. Line 4 shows the day and month with the year in a four-digit format. Line 5 uses the DAY format mask to spell out the day of the week. Line 6 uses the MONTH format mask to spell out the month. Line 8 specifies that the sample table created in Step 1 is the source of the data.
3. Run the query to display the output. Figure 2.2 shows the output of the query within SQL*Plus.
The first three columns of the query display the date in the most commonly used formats. The DAY and MONTH columns show how the date can be converted and displayed in different formats. Both columns are shown in uppercase letters. If the format mask were specified in lowercase, or with only the first letter uppercase, the case of the output would change.
4. Load CHP2_3.SQL into the SQL buffer, shown in Figure 2.3. The query contained in the file presents more formats in which date values can be presented.
Line 2 displays the day of the week abbreviated in the three-digit format. Line 3 uses the DDD format mask to display the number of days from the beginning of the year. Line 4 uses the WW format mask to display the week of the year from 1 to 53. Line 5 uses the MON format mask to display the month in its three-digit abbreviation. Line 6 uses the YEAR format mask to display the year spelled out.
5. Run the statement to display the formatted results. Figure 2.3 shows the results of the query.
The formats displayed by the query can be used for specific requirements within applications. Information such as the day of year or week of year can be useful in batch operations.
6. Load CHP2_4.SQL into the SQL buffer, shown in Figure 2.4. The query contained in the file displays format masks based on the year and the time portion of the date variable.
Line 2 uses the RM format mask to display the month of the year as a Roman numeral. Line 3 uses the CC format mask to format the century. Line 4 uses the HH format mask to display the hour of the day from 0 to 12. Line 5 uses the HH format mask with the PM modifier to display the hour of the time with AM or PM. Line 6 uses the HH24 format mask to display the hour in military time. Line 7 formats the minutes with the MI format mask. Line 8 uses the SS format mask to display the seconds. Line 9 uses the SSSSS format mask to display the number of seconds past midnight.
7. Run the statement to display the results.
The results of the query show how year values can be presented as Roman numerals and the century can be calculated. The time formats contained in the query show that the time portion of the date column can be displayed in a variety of ways (see Figure 2.5).
8. Load CHP2_5 into the SQL buffer. The NEW_TIME function in the following statement returns the current time, converting the time of the system clock, which is on Greenwich mean time (GMT), to Eastern standard time (EST). This is shown in Figure 2.6.
9. Load CHP2_6 into the SQL buffer. This command returns the current time, converting the time of the system clock, which is on GMT, to Central standard time (CST). This is shown in Figure 2.7.
Notice the one hour difference between the results from CHP2_5.SQL and CHP2_6.SQL. The NEW_TIME function is important for database operations that span time zones. Users in Japan, New York, and Hawaii can get the date and time as it should appear in their individual time zones.
How It Works
The TO_CHAR function is used to display a date value in a variety of formats. You can use the format models shown in Table 2.1 to create date output in almost any conceivable format. The opposite of the TO_CHAR function is the TO_DATE function. Use the TO_DATE function to convert a character string to the internal Oracle date format. Step 1 creates the sample table and data used throughout this How-To. The sample table contains a date column and a single record, which are queried in the later steps. Steps 2 through 9 present queries that format the date column to present formatted output.
The NEW_TIME function converts and displays the time portion of a date value. The valid format models of the zone_input and zone_output portions of the function are shown in Table 2.2.
Comments
Date and time values can be formatted easily using the TO_CHAR function. The power of the function can solve a variety of problems. You can use the date formatting functions to write numbers as text. A common mistake is to incorrectly select the date model MM for minutes; use MM only for months.