A Blog by Franklin Pettit

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.

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

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

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

Format	Description	Example
DD-MON-YY	Day-Month-Year	05-MAY-21
DD-MON-YYYY	Day-Month-Four Digit Year	05-MAY-2021
MM/DD/YYYY	Month/Day/Four Digit Year	05/05/2021
DD/MM/YYYY	Day/Month/Four Digit Year	05/05/2021
DD-MM-YY	Day-Month-Two Digit Year	05-05-21
DD-MM-YYYY	Day-Month-Four Digit Year	05-05-2021
MON-DD-YY	Month-Day-Two Digit Year	MAY-05-21
MONTH DD, YYYY	Month Day, Four Digit Year	May 05, 2021
DD/MM/YY HH24:MI:SS	Day/Month/Two Digit Year Hour:Minute:Second	05/05/21 20:30:00
DD-MON-YY HH:MI:SS PM	Day-Month-Two Digit Year Hour:Minute:Second AM/PM	05-MAY-21 08:30:00 PM
YYYY-MM-DD	Four Digit Year-Month-Day	2021-05-05
YYYY-MM-DD HH24:MI:SS	Four Digit Year-Month-Day Hour:Minute:Second	2021-05-05 20:30:00

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.

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.

MM	Numeric month (e.g., 07)
MON	Abbreviated month name (e.g., JUL)
MONTH	Full month name (e.g., JULY)
DD	Day of month (e.g., 24)
DY	Abbreviated name of day (e.g., FRI)
YYYY	4-digit year (e.g., 1998)
YY	Last 2 digits of the year (e.g., 98)
RR	Similar 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
HH	Hour of day (1-12)
HH24	Hour of day (0-23)
MI	Minute (0-59)
SS	Second (0-59)

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/