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

By M&S Consulting (Ashok) on Jun 8th, 2009
Filed Under Technical Tips // Tags:

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
Bookmark and Share

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

Leave a Reply


Archives

Recent Comments

  • SaintClair said: Thanks a million for this priceless material. May God richly bless you!
  • ashok.aggarwal said: Thank you for your comment, Stuart. I’m glad this helped you.
  • Stuart said: I’d really like to thank you for your post. It was really that simple.
  • ashok.aggarwal said: Your specific question is not very clear, however, in the context of implementing a LIKE...
  • Jonas said: Finally in print that a user is required to show the email option. Thanks.

Calendar

March 2010
M T W T F S S
« Feb    
1234567
891011121314
15161718192021
22232425262728
293031  

Featured Testimonial

"M&S has taken 'partnership' to a new level. They are not only a deeply skilled service provider, the M&S team is actually a mission critical component to our operations, taking on new initiatives as well as maintaining custom, open source, and COTS solutions."

CEO, MedPivot

Free SQL Service