You will love this blog because date functions and formatting differ dramatically between databases, but I have covered that with brilliant examples and clear and concise explanations below.

All of these examples have come from my books and training classes. Please do me a favor and tell your training coordinator that you know the best technical trainer in the world. Ask them to hire me to train at your company, either on-site or with a virtual class. They can see our classes, outlines, and a sample of my teaching at this link on our website.

https://coffingdw.com/education/

If you are looking for a better way to access and develop data on Oracle or any system, I hope you will download my Nexus Query Chameleon software, which works on all systems and is the best integration and query federation tool ever built. It has taken 19 years to join 20 tables spanning 20 different systems in a single query, but nobody else is even close to that capability. You can now use Nexus to join tables and views from Oracle with Excel worksheets and Microsoft Access, and any combination of cloud and on-premises databases in a single query. Check out the video below and see Nexus in action.

Most databases will allow you to query and receive the current date or timestamp without a FROM clause, but Oracle insists that every query use a FROM clause. So, when all you need to do is receive the CURRENT_DATE, SYSDATE, or CURRENT_TIMESTAMP, Oracle has a dummy table for dates called DUAL. As you can see from the examples below, we are receiving date and timestamp information and satisfying Oracle’s FROM clause rule by pulling the data from the dummy table DUAL.

The first query in the example below retrieves the current system date and time. The second query retrieves various components of the current system date and time. It fetches today’s date, the day, month, and year components separately and presents the date and time in a specific format (‘DD-MON-YYYY HH:MI:SS’) as a timestamp.

The first query in the example below retrieves the current date using the ANSI SQL standard function CURRENT_DATE and aliases it as ANSI_DATE.

The second query performs several operations on the current date. It retrieves the current date, calculates tomorrow’s date by adding one day to the current date (CURRENT_DATE + 1), calculates yesterday’s date by subtracting one day from the current date (CURRENT_DATE – 1), and computes the difference in days between yesterday and today by subtracting yesterday’s date from today’s date. These operations are executed within the context of the DUAL table, a special table in Oracle used for performing calculations without referencing any actual table data.

The example below is the fundamental way in which most databases retrieve the current date and current timestamp, which are keywords using the ANSI standard. For clarity, it assigns aliases ANSI_DATE and ANSI_TIMESTAMP to these values.

In the example below, notice the CURRENT_TIMESTAMP includes an offset of “-04:00”, which suggests a time zone offset of 4 hours behind Coordinated Universal Time (UTC-4). The difference between CURRENT_DATE and SYSDATE is that CURRENT_DATE returns the date portion without any time zone information. In contrast, SYSDATE returns the date and time in the server’s time zone, attributed to the time zone offset between the client and the server where the query is executed.

In our next example below, we see that there is not only a CURRENT_DATE and a SYSDATE but also a CURRENT_TIMESTAMP and a SYSTIMESTAMP. The CURRENT_DATE and CURRENT_TIMESTAMP return the date and timestamp without any time zone information. In contrast, SYSDATE and SYSTIMESTAMP return the date and time in the server’s time zone, attributed to the time zone offset between the client and the server where the query is executed.

In Oracle, both CURRENT_TIMESTAMP and LOCALTIMESTAMP functions return the current date and time. However, there are differences between them:

CURRENT_TIMESTAMP: This function returns the current date and time, including the time zone information.

LOCALTIMESTAMP: This function returns the current date and time in the session’s time zone without the time zone offset.

This first query retrieves the current timestamp twice. The first column fetches the current timestamp with default precision, which could vary depending on the Oracle instance settings but typically includes fractions of seconds with microsecond precision. The second column retrieves the current timestamp with a precision of 3, indicating milliseconds precision.

The second query retrieves the current local timestamp twice. The first column fetches the local timestamp with default precision, typically including fractions of seconds with microsecond precision. The second column retrieves the local timestamp with a precision of 2, indicating hundredths of a second (centiseconds) precision.

Now, regarding precision levels:

Precision level 1: Represents seconds.
Precision level 2: Represents hundredths of a second (centiseconds).
Precision level 3: Represents milliseconds.
Precision level 4: Represents ten-thousandths of a second (ten-milliseconds).
Precision level 5: Represents hundred-thousandths of a second (tenth-milliseconds).
Precision level 6: Represents microseconds.

This Oracle query below retrieves the current timestamp using the CURRENT_TIMESTAMP function and generates a timestamp with time zone information using the FROM_TZ function. This query uses CURRENT_TIMESTAMP to fetch the current timestamp and FROM_TZ to generate a timestamp with specified time zone information.

Other time zones you can try are listed below:

‘America/Los_Angeles’
‘America/New_York’
‘Europe/London’
‘Asia/Tokyo’
‘Australia/Sydney’

When you add or subtract from a date, you are adding/subtracting days. And yes, Oracle knows the calendar and the saying, “30 days hath September, April, June, and November. All the rest have 31 except February alone, to which we 28 assign, but leap year makes it 29.”

In the Oracle SQL query below, the ADD_MONTHS function calculates a new date by adding a specified number of months to the given date. Notice that we are adding two months to the ORDER_DATE. The result of this operation is aliased as “Due Date2”. The ADD_MONTHS function allows for easy manipulation of dates by adding or subtracting months, which can be useful for tasks like forecasting due dates or scheduling activities based on the order date.

There is no ADD_YEAR command, so in the query below we use the ADD_MONTHS command using 12 months. The example that follows will show you a clever way to use the ADD_MONTHS command to add several years by performing math.

In Oracle, the EXTRACT command extracts a specific component (such as year, month, day, hour, minute, etc.) from a date or timestamp value. It allows users to retrieve and work with individual parts of a date or timestamp data. The query below retrieves orders from the ORDER_TABLE where the month of the order date is September. It utilizes the EXTRACT command to extract the month from the ORDER_DATE column and then filters the results based on that extracted month value.

The query below uses the EXTRACT command to extract a specific component (such as year, month, day, hour, minute, second.) from a date or timestamp value. It allows users to retrieve and work with individual parts of a date or timestamp data.

The query below provides comprehensive information about the current date, time, ISO week number, quarter, day of the week, and timezone offset.

The query below retrieves data from the ORDER_TABLE, extracting and formatting the day, month, and year components from the ORDER_DATE. It also filters the results to include only orders from the year 2022. The TO_CHAR function is used as an implicit extraction tool to format the date components as required for display and filtering.

The ROUND function in Oracle works on dates by rounding them to the nearest specified unit (year, month, or day), effectively truncating the date to the nearest boundary of that unit. Here is how each unit works in the query below:

‘YEAR’ – If the date falls between January 1 and June 30, it rounds down to the previous year. If it falls between July 1 and December 31, it rounds up to the next year.

‘MONTH’ – It rounds to the beginning of the current month if the day is 15 or less and to the beginning of the next month if the day is greater than 15.

‘DAY’ – It rounds to midnight of the current day if the time is 12 PM or earlier and to midnight of the next day if the time is after 12 PM.

The LAST_DAY function in Oracle finds the last day of the month for a given date. It takes a date or timestamp as input and returns the date corresponding to the last day of the month for that input date. If the input date is already the last day of the month, it simply returns that date.

The first query below uses the LAST_DAY function to find the last day of the month for both the current date (CURRENT_DATE) and the current timestamp (CURRENT_TIMESTAMP). The second query below uses the LAST_DAY function to find the last day of the month for the SYSDATE. Additionally, the second query calculates the number of days until the end of the month by subtracting the system date (SYSDATE) from the last day of the month.

The NEXT_DAY function in Oracle is used to find the date of the next occurrence of a specified day of the week following a given date. It takes two parameters: a date expression representing the starting date and a day name specified as a string literal (‘Monday,’ ‘Tuesday,’ ‘Wednesday,’ ‘Thursday,’ ‘Friday,’ ‘Saturday,’ or ‘Sunday’) representing the target day of the week.

The query below retrieves the current date and calculates the dates for next Wednesday, Thursday, and Friday, respectively, following the current date. It demonstrates using the NEXT_DAY function to find future occurrences of specific days of the week.

The MONTHS_BETWEEN function in Oracle calculates the number of months between two dates or timestamps. It takes two date or timestamp expressions as arguments and returns the difference in months between them. If the second date is later than the first date, the result is positive; if the second date is earlier, the result is negative. Notice the plus and minus dates in our query examples below.

The TRUNC function in Oracle is used to truncate a date or timestamp to a specified level of precision. It returns the date or timestamp with the time portion truncated to the nearest specified unit (year, month, day, etc.).

Both queries below utilize the TRUNC function to truncate the system date to different levels of precision (year, month, and day). The first query truncates it to the beginning of the year and the month, while the second query truncates the beginning of the week.

Posted in Blog