More About Oracle SQL Dates

I wrote about Oracle SQL dates yesterday. Here is some additional data. Dates are an integral part of any database system, and Oracle SQL is no exception. Oracle SQL offers a wide range of date formats that allow you to store, retrieve, and manipulate dates in a variety of ways. In this blog post, we will explore the various date formats available in Oracle SQL and how to use them effectively.

Date Formats in Oracle SQL

Oracle SQL supports a variety of date formats, including the following:

  1. Date Format

The default date format in Oracle SQL is ‘DD-MON-YY,’ which displays the date in the format of “Day-Month-Year.” For example, 05-MAY-21 represents May 5, 2021.

  1. Time Format

The default time format in Oracle SQL is ‘HH:MI:SS AM,’ which displays the time in hours, minutes, and seconds. The ‘AM’ or ‘PM’ indicator is used to represent the time in either morning or afternoon. For example, 08:45:30 PM represents 8:45 PM.

  1. Timestamp Format

The timestamp format in Oracle SQL is ‘DD-MON-YY HH:MI:SS.FF,’ which displays the date and time up to six decimal places. For example, 05-MAY-21 08:45:30.000000 represents May 5, 2021, at 8:45 PM and 30 seconds with zero milliseconds.

  1. Interval Format

The interval format in Oracle SQL is used to represent time durations, such as hours, minutes, and seconds. The interval format is expressed as ‘INTERVAL [n] [unit],’ where ‘n’ represents the duration and ‘unit’ represents the unit of time. For example, INTERVAL ‘5’ HOUR represents a duration of five hours.

Using Date Formats in Oracle SQL

To use date formats in Oracle SQL, you can use the ‘TO_DATE’ function. The ‘TO_DATE’ function converts a character string to a date value using the specified date format.

For example, the following query converts a character string to a date value using the ‘DD-MON-YY’ date format:

SELECT TO_DATE(’05-MAY-21′, ‘DD-MON-YY’) FROM DUAL;

The output of the above query would be ’05-MAY-21.’

In addition to the ‘TO_DATE’ function, Oracle SQL provides several other date functions, such as ‘SYSDATE,’ which returns the current date and time, and ‘ADD_MONTHS,’ which adds a specified number of months to a date.

Here are some common formats and and their usage:

FormatDescriptionExample
DD-MON-YYDay-Month-Year05-MAY-21
DD-MON-YYYYDay-Month-Four Digit Year05-MAY-2021
MM/DD/YYYYMonth/Day/Four Digit Year05/05/2021
DD/MM/YYYYDay/Month/Four Digit Year05/05/2021
DD-MM-YYDay-Month-Two Digit Year05-05-21
DD-MM-YYYYDay-Month-Four Digit Year05-05-2021
MON-DD-YYMonth-Day-Two Digit YearMAY-05-21
MONTH DD, YYYYMonth Day, Four Digit YearMay 05, 2021
DD/MM/YY HH24:MI:SSDay/Month/Two Digit Year Hour:Minute:Second05/05/21 20:30:00
DD-MON-YY HH:MI:SS PMDay-Month-Two Digit Year Hour:Minute:Second AM/PM05-MAY-21 08:30:00 PM
YYYY-MM-DDFour Digit Year-Month-Day2021-05-05
YYYY-MM-DD HH24:MI:SSFour Digit Year-Month-Day Hour:Minute:Second2021-05-05 20:30:00
Note that these are just a few of the many date formats available in Oracle SQL, and you can create your own custom date formats using the ‘TO_CHAR’ function.

Conclusion

In conclusion, Oracle SQL provides a wide range of date formats that allow you to store, retrieve, and manipulate dates in a variety of ways. Understanding the various date formats and how to use them effectively can help you work more efficiently with date data in your Oracle SQL database.

Oracle Date Formats

I have been writing Oracle SQL for many years but I constantly look up Oracle date formats. It is interesting what your brain stores and what it doesn’t. See below for common ones. I hope this helps someone.

MMNumeric month (e.g., 07)
MONAbbreviated month name (e.g., JUL)
MONTHFull month name (e.g., JULY)
DDDay of month (e.g., 24)
DYAbbreviated name of day (e.g., FRI)
YYYY4-digit year (e.g., 1998)
YYLast 2 digits of the year (e.g., 98)
RRSimilar to YY, but year digits are “rounded” to a year in the range of 1950 to 2049. So, 06 returns 2006 instead of 1906.
AM (or PM)Meridian indicator
HHHour of day (1-12)
HH24Hour of day (0-23)
MIMinute (0-59)
SSSecond (0-59)
These can be used with TO_CHAR or TO_DATE. Like this: TO_DATE(ADDED_DATE, ‘DD-Mon-YYYY HH24:MI:SS’)

Here are some links with more helpful information about Oracle dates.

http://infolab.stanford.edu/~ullman/fcdb/oracle/or-time.html
https://www.oracletutorial.com/oracle-basics/oracle-date-format/