Oracle TO_CHAR Function – SQL Syntax Examples (Most With Dates, TO_DATE)

Posted on Jun 8, 2009 in Technical Tips | 0 comments

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...

2. First and Last Day of Month – Oracle SQL Syntax Examples

   First Day of Current Month...

3. Oracle INSTR Function – SQL Syntax Examples

   The Oracle INSTR SQL function...

4. Oracle SUBSTR Function – SQL Syntax Examples

   The Oracle SUBSTR SQL Function...

5. Oracle SUBSTR with INTSR Function – SQL Syntax Examples

   Oracle SUBSTR and INSTR SQL...

6. Oracle APEX Tutorial 7 – Stacked Bar Chart – Video Training

   Entering and retrieving data is...

7. SQL LIKE – Oracle LIKE Operator – SQL Syntax Examples

   The reserved word LIKE in...

8. XMLFOREST vs XMLELEMENT – Missing vs Empty in SQLX

   Like me, some people may...