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