Date and Timestamp Format Elements
These format elements can be used together to create the format string for TO_DATE and TO_TIMESTAMP.
Format element | Unit | Example |
|---|---|---|
YYYY / yyyy | Year (4 and more digits) | 2025-01-01 |
YYY / yyy | Year (last 3 digits) | 025-01-01 |
YY / yy | Year (last 2 digits) | 25-01-01 |
Y / y | Year (last digit) | 5-01-01 |
MM / mm | Month (01-12, two digits) | 2025-01-01 |
M / m | Month (1-12, one or two digits) | 2/2/2029 |
Month | Month (full name) | 01 January 2025 |
Mon | Month (abbreviated mixed-case name) | 01 Jan 2025 |
MON | Month (abbreviated uppercase name) | 01 JAN 2025 |
DDD / ddd | Day of year (001-366) | 2025-001 |
DD / dd | Day of month (01-31, two digits) | 2025-01-01 |
D / d | Day of month (1-31, one or two digits) | 2/2/2029 |
HH / hh / HH24 / hh24 | Hour of day (00-23) | 14:00:00 |
HH12 / hh12 | Hour of day (01-12) | 02:00:00 PM |
MI / mi | Minute (00-59) | 14:00:00 |
SS / ss | Second (00-59) | 14:00:00 |
MS / ms / FF3 / ff3 | Millisecond (000-999) | 14:00:00.123 |
US / us / FF6 / ff6 | Microsecond (000000-999999) | 14:00:00.123456 |
AM / am / PM / pm | Meridian indicator | 02:00:00 PM |
Use MM/DD when the input always has zero-padded month/day (e.g. 01/01/2025). Use M/D when the input may have variable-width values (e.g. 2/2/2029 or 12/25/2029).