Skip to Main Content

Write Great SQL Office Hours

Free tips and training every month! Subscribe for reminders and more from Office Hours. FAQ

Header container

January 17, 2023

14:00 UTC   Start Times Around the World

Subscribe to be notified of changes to sessions and give us feedback!

Having trouble watching the video on this page? Open the video in your browser.


How to use DATEs, TIMESTAMPs, and INTERVALs in Oracle Database
Oracle Database has three core data types for storing datetime values: DATE, TIMESTAMP, and INTERVAL. The database provides many functions to help you work with these types.

Watch this back-to-basics session to learn how to use datetime values in Oracle Database. Highlights include:

3:10 - Data types; DATE, TIMESTAMP, INTERVAL
6:30 - Why implicit conversion errors happen and how to stop them
9:45 - Default on conversion error clause
12:40 Validate_conversion function
15:00 Setting the default display format for datetime values
17:10 DATE and TIMESTAMP literals
18:15 TRUNC to round down dates
19:20 String-to-date conversion demos
25:30 Flexible format matching demo
32:25 Datetime to string conversion demo
34:10 Cast DATE <> TIMESTAMP demo
35:30 Interval literals and conversion demo
37:05 Datetime arithmetic
38:50 Get a duration in seconds
40:30 Adding or removing months
40:55 Datetime arithmetic demo
42:45 Adding or removing months demo
53:00 Dealing with time zones
56:35 Demo of time zone logic
1:03:50 Final recap

Learn SQL

Your Experts

    Chris Saxon

    Chris Saxon   

    Chris Saxon is an Oracle Developer Advocate and quizmaster on Dev Gym. His job is to help you get the best out of the Oracle Database and have fun with SQL! He's also part of the answer team on Ask TOM and creates YouTube videos at The Magic of SQL channel.