Let’s review Oracle date formatting.
This is useful for functions like TO_DATE, TO_CHAR or alter session to modify nls_date_format.
Format table
| Formato | Descripción | Resultado |
| Y | Last digit of the year | 7 |
| YY | Last two digits of the year | 17 |
| YYY | Last three digits of the year | 017 |
| YYYY | Four-digit year | 2017 |
| RR | Two-digit year | 17 |
| YEAR | Year in English | TWENTY SEVENTEEN |
| MM | Two-digit month | 02 |
| MON | Three-letter month | FEB |
| MONTH | Month in English | FEBRUARY |
| D | Day of the week | 5 |
| DD | Day of the month (2 digits) | 07 |
| DDD | Day of the year | 212 |
| DY | Three-letter day of the week | FRI |
| DAY | Day in English | FRIDAY |
| W | Week of the month | 3 |
| WW | Week of the year | 43 |
| Q | Quarter of the year | 1 |
| CC | Century | 10 |
| SCC | Same as CC, but it adds a minus in case of BC | -3 |
| SYYYY | Same as YYYY, but it adds a minus in case of BC | -734 |
| SYEAR | Same as YEAR, but it adds a minus in case of BC | -THIRTY THREE |
| BC, AD, B.C. , A.D. | Adds BC, AD, B.C. or A.D. | AD |
| J | Days since 31 December 4713 BC | 1356021 |
| IW | ISO standard week 1-53 | 32 |
| RM | Roman numeral month | II |
| AM, PM, A.M. , P.M. | Meridian indicators | PM |
| HH | Hour of day | 03 |
| HH12 | Hour of day 1-12 | 11 |
| HH24 | Hour of day 0-23 | 14 |
| MI | Minute 0-59 | 34 |
| SS | Second 0-59 | 58 |
| SSSSS | Seconds since midnight 0-86399 | 32098 |
| /.,?#! | Punctuation marks, for instance: ‘MM.YY’ | 14.10 |
| “literals” | For instance, ‘”Week” W “of” Month’ | Week 3 of February |
| TH | DDth | 12TH |
| SP | Spelled out number, for instance, MM | Nine |
| THSP or SPTH | Spelled out positional or ordinal number, for example: ‘hh24Spth’ | Fourtheenth |
Example
We are going to alter our session to modify date format.
In this case we are connected to sqlplus, and this is what we execute:
alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';
Dates will be represented like: 18/12/2017 14:27:17