Common TO_CHAR Examples with Dates and the TO_DATE Function
Formatting Hours in Dates
24 Hour – (aka military time) represented by HH24
1
| SELECT to_char(sysdate, 'MM/DD/YYYY HH24:MI') FROM dual |
select to_char(sysdate, 'MM/DD/YYYY HH24:MI') from dual
12 Hour – represented by HH
or HH12
and using the meridian indicator AM, PM, A.M., P.M. (and all possible in lower case as well)
Note: You will see that we added 12 hours to the current date with SYSDATE+1/2
1
| SELECT to_char(sysdate+1/2, 'MM/DD/YYYY HH:MI AM') FROM dual |
select to_char(sysdate+1/2, 'MM/DD/YYYY HH:MI AM') from dual
Adding Time to a Date
1 Day (24 Hours)
1
| SELECT to_char(sysdate+1, 'MM/DD/YYYY HH:MI AM') FROM dual |
select to_char(sysdate+1, 'MM/DD/YYYY HH:MI AM') from dual
12 Hours
1
| SELECT to_char(sysdate+1/2, 'MM/DD/YYYY HH:MI AM') FROM dual |
select to_char(sysdate+1/2, 'MM/DD/YYYY HH:MI AM') from dual
1 Hour
1
| SELECT to_char(sysdate+1/24, 'MM/DD/YYYY HH:MI AM') FROM dual |
select to_char(sysdate+1/24, 'MM/DD/YYYY HH:MI AM') from dual
5 Minutes
1
| SELECT to_char(sysdate+1/24/60*5, 'MM/DD/YYYY HH:MI AM') FROM dual |
select to_char(sysdate+1/24/60*5, 'MM/DD/YYYY HH:MI AM') from dual