Dates in oracle are stored in a format that doesn’t always make them friendly from a reporting point of view or dealing with data in Excel point of view, the TO_CHAR function can reformat your datetime fields so that they are more user friendly.
Examples
The statement is written with two parameters the exact name of the date field and the format (see second table).
TO_CHAR(DATEFIELD,’FORMAT’)
Statement
|
Result
|
TO_CHAR(DATEFIELD,’dd mm year’)
|
23 05 twenty thirteen
|
TO_CHAR(DATEFIELD,’dd/mm/yy’)
|
23/05/2013
|
TO_CHAR(DATEFIELD,’Day Month Year’)
|
23 May Twenty Thirteen
|
TO_CHAR(DATEFIELD,’dd/mm/yyyy hh24:mi’)
|
23/05/2013 18:38
|
Example formats
Any combination of the following formats can be used, although obviously certain combinations may not make any sense to your end users.
Format
|
Description
|
Example
|
Year/year
|
Year spelt out in text (with and without an upper case first digit
|
Twenty Thirteen / twenty thirteen
|
yyyy
|
Year number in full
|
2008
|
yy
|
Last two digits of year number
|
08
|
q
|
Quarter of the year
|
1 (February)
|
mm
|
Month number within year**
|
12 (December)
|
Mon / mon
|
Abbreviated month name (with and without an uppercase first digit)
|
Oct/oct
|
Month / month
|
Month name (with and without an upper case first digit)
|
October / october
|
w
|
Week number* (within month)
|
1 (01/05/2013)
|
ww
|
Week number* (within year) **
|
18 (01/05/2013)
|
d
|
Day of the week
|
1 (Monday)
|
dd
|
Day number within month**
| |
Dy/dy
|
Abbreviated day name (with and without an uppercase first digit)
|
Mon / mon
|
Day / day
|
Day name (with and without an upper case first digit)
|
Monday / Monday
|
hh24
|
Hour of the day in 24 hour format**
|
16 (4pm)
|
hh
|
Hour of the day in 12 hour format**
|
04 (4pm)
|
mi
|
Minute of the hour**
|
52 (16:52)
|
*Important note about week numbers
Week numbers in oracle can be confusing as they start on the first day of the year and count seven days and then change, for example in 2013 the week number would increase each Wednesday.
**Suppression of zeros
This format can be prefixed with FM to suppress zeros, for example mm would display January as “01” where as if FMmm was used it would show “1”.
How to test you date formatting
The easiest method for testing the reformatting of your dates is to test them using the dual function, run the following SQL statement and substitute the area highlighted in yellow with the date format you are trying to use.
select to_char(to_date('01/05/2013 16:30','dd/mm/yyyy hh24:mi'),'Year') from dual
No comments:
Post a Comment