Oracle offers a rich set of built-in variables and functions designed to manipulate DATE and TIMESTAMP values. These tools empower users to perform intricate operations on temporal data, enhancing the flexibility and efficiency of data processing within the Oracle SQL environment.
SYSDATE
The SYSDATE is a special variable that holds the system's current date and time. When you print out SYSDATE in a SELECT statement, it will show you DATE type value, which is in the format 'DD-MON-YY'.
The SYSDATE is based on the server's system clock where Oracle database is installed. So, even when your SQL statement is running on an instance from a remote client location, SYSDATE will return the date and time of the operating system on which the server resides.
ROUND(d, i) and TRUNC(d, i)
When the ROUND function is applied to a date value (denoted as 'd'), it rounds off the value based on a specified level of detail ('i'). 'd' is a required argument on which you wish to perform operations, and 'i' is an optional format model that dictates the precision to which 'd' should be rounded; if 'i' is omitted, ROUND will round 'd' to the nearest whole day by default.
In the example shown above, '2023-05-31' rounded down to month ('2023-06-01') and year ('2023-01-01'). The desired output formats are specified with 'YYYY' and 'MM'. Similarly, when the ROUND function is applied to '2023-12-31', we can observe that the value is rounded up to the specified level, which is '2024-01-01'.
Note: For ROUND function, values are biased toward rounding up. For example, when rounding off time, 12 noon rounds up to the next day.
The TRUNC function shares both the required and optional arguments with ROUND, and it serves the similar purpose as ROUND when applied to dates. However, unlike the ROUND function, which returns rounded date and time values, TRUNC consistently disregards (rounds down). For example:
In the figure shown above, we can observe that the date value '2023-12-31' is truncated to the first day of the month and year, which is different from the output of the ROUND function.
NEXT_DAY(d, c)
The NEXT_DAY function takes two required arguments: a date string 'd' and another string 'c', indicating the day of the week. Then it returns the first occurrence of the c day after the date in d. For example, to calculate the first Sunday comes after March 25th in 2024 in Oracle SQL:
LAST_DAY(d)
The LAST_DAY function in Oracle SQL requires one argument, 'd', a date string. The function returns the last day of the month in which the specified date 'd' is situated. For example:
ADD_MONTHS(d, n)
The ADD_MONTH function in Oracle SQL takes two required arguments: a date string 'd' and a whole number 'n'. This function adds 'n' months to the input date 'd'. For example:
In the figure shown above, I added and subtracted some months to date strings. Note that, in Oracle, there is no distinct function to subtract months. Instead, just add a negative number of months in ADD_MONTHS function.
MONTHS_BETWEEN(d1, d2)
The MONTHS_BETWEEN function calculates the number of months between the two date values, requiring both 'd1' and 'd2' as required parameters to determine the duration in months between the specified dates. For example:
In the example above, I input '2026-12-01' as 'd1' and '2024-03-01' as 'd2'. Then the MONTHS_BETWEEN determines how many months pasts from 'd2' to 'd1'. So, if you input the date values in a chronological order, you will see a negative value. For example:
Note that the MONTHS_BETWEEN function does not round off the resulting value automatically; if the result is a partial month, it will show you a real number. For example:
EXTRACT(field FROM source)
The EXTRACT function extracts a specific component, such as year, month, day, hour, etc., from a DATE or TIMESTAMP value. It offers a direct and convenient way to isolate specific components without needing other functions or conversions. The function takes two arguments, 'field' and 'source', where 'field' specifies the component to extract, and 'source' is the DATE or TIMESTAMP value from which to extract the component. In between the two arguments, you should also place the FROM keyword. For example:
0 Comments