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

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

Adding Time to a Date

1 Day (24 Hours)

1

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

1 Hour

1

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

Related Information:

1. Oracle TO_DATE Function - SQL Syntax Examples The Oracle TO_DATE SQL function is used to convert a TEXT or NTEXT representation of a date into an Oracle...
2. Oracle INSTR Function - SQL Syntax Examples The Oracle INSTR SQL function is popular and performs materially the same operation as instr functions in many other programming...
3. Oracle SUBSTR Function - SQL Syntax Examples The Oracle SUBSTR SQL Function is very common, and many times misused. Below shows the SUBSTR function along with the...
4. Oracle SUBSTR with INTSR Function - SQL Syntax Examples Oracle SUBSTR and INSTR SQL functions are typically used together in practice for parsing a string. Following are...
5. Oracle APEX Tutorial 7 - Stacked Bar Chart - Video Training Entering and retrieving data is all well and good, but sometimes a visual display of the raw numbers gives more...
6. SQL LIKE - Oracle LIKE Operator - SQL Syntax Examples The reserved word LIKE in Oracle SQL represents an operator that performs some basic matching on text. LIKE provides a...
7. Oracle APEX Tutorial 4 – Form Layout – Part 1 – Video Training APEX gives you a lot of power and flexibility with its built in wizards. They are very good at creating...
8. XMLFOREST vs XMLELEMENT - Missing vs Empty in SQLX Like me, some people may have gotten excited about using XMLFOREST in SQLX, only to find that NULL values result...