Oracle provides a comprehensive set of date functions to handle various date and time operations. Here are the most commonly used date functions along with examples:
1. SYSDATE: Returns the current date and time from the system.
SELECT SYSDATE AS current_date_time FROM DUAL; -- Returns current date and time
2. CURRENT_DATE: Returns the current date in the session time zone.
SELECT CURRENT_DATE AS current_date FROM DUAL; -- Returns current date in session time zone
3. CURRENT_TIMESTAMP: Returns the current date and time in the session time zone, including fractional seconds.
SELECT CURRENT_TIMESTAMP AS current_timestamp FROM DUAL; -- Returns current timestamp in session time zone
4. SYSTIMESTAMP: Returns the current date and time from the system, including fractional seconds and time zone.
SELECT SYSTIMESTAMP AS system_timestamp FROM DUAL; -- Returns current timestamp with time zone
5. LOCALTIMESTAMP: Returns the current date and time in the session time zone without the time zone.
SELECT LOCALTIMESTAMP AS local_timestamp FROM DUAL; -- Returns current timestamp without time zone
6. DBTIMEZONE: Returns the time zone of the database.
SELECT DBTIMEZONE AS db_time_zone FROM DUAL; -- Returns database time zone
7. SESSIONTIMEZONE: Returns the time zone of the session.
SELECT SESSIONTIMEZONE AS session_time_zone FROM DUAL; -- Returns session time zone
8. EXTRACT: Extracts and returns the value of a specified date part from a date.
SELECT EXTRACT(YEAR FROM SYSDATE) AS year_extracted FROM DUAL; -- Returns the current year
SELECT EXTRACT(MONTH FROM SYSDATE) AS month_extracted FROM DUAL; -- Returns the current month
SELECT EXTRACT(DAY FROM SYSDATE) AS day_extracted FROM DUAL; -- Returns the current day
9. ADD_MONTHS: Adds a specified number of months to a date.
SELECT ADD_MONTHS(SYSDATE, 6) AS date_plus_6_months FROM DUAL; -- Returns date 6 months from now
SELECT ADD_MONTHS(SYSDATE, -6) AS date_minus_6_months FROM DUAL; -- Returns date 6 months ago
10. MONTHS_BETWEEN: Returns the number of months between two dates.
SELECT MONTHS_BETWEEN(SYSDATE, '2023-01-01') AS months_diff FROM DUAL; -- Returns months between current date and January 1, 2023
SELECT MONTHS_BETWEEN('2023-06-01', '2023-01-01') AS months_diff FROM DUAL; -- Returns 5
11. NEXT_DAY: Returns the date of the next specified weekday after a given date.
SELECT NEXT_DAY(SYSDATE, 'MONDAY') AS next_monday FROM DUAL; -- Returns the next Monday after the current date
SELECT NEXT_DAY('2024-06-01', 'FRIDAY') AS next_friday FROM DUAL; -- Returns the next Friday after June 1, 2024
12. LAST_DAY: Returns the last day of the month that contains a specified date.
SELECT LAST_DAY(SYSDATE) AS last_day_of_month FROM DUAL; -- Returns the last day of the current month
SELECT LAST_DAY('2024-06-01') AS last_day_of_june FROM DUAL; -- Returns June 30, 2024
13. TRUNC (Date): Truncates a date to a specified unit of measure.
SELECT TRUNC(SYSDATE, 'YEAR') AS trunc_year FROM DUAL; -- Returns the first day of the current year
SELECT TRUNC(SYSDATE, 'MONTH') AS trunc_month FROM DUAL; -- Returns the first day of the current month
SELECT TRUNC(SYSDATE, 'DAY') AS trunc_day FROM DUAL; -- Returns the current date with time set to midnight
14. ROUND (Date): Rounds a date to a specified unit of measure.
SELECT ROUND(SYSDATE, 'YEAR') AS round_year FROM DUAL; -- Returns the first day of the next year if the current date is July 1st or later, otherwise the first day of the current year
SELECT ROUND(SYSDATE, 'MONTH') AS round_month FROM DUAL; -- Returns the first day of the next month if the current date is the 16th or later, otherwise the first day of the current month
15. NEW_TIME: Converts a date and time from one time zone to another.
SELECT NEW_TIME(SYSDATE, 'EST', 'PST') AS new_time FROM DUAL; -- Converts current date and time from EST to PST
16. TO_DATE: Converts a string to a date.
SELECT TO_DATE('2023-01-01', 'YYYY-MM-DD') AS converted_date FROM DUAL; -- Converts string to date
SELECT TO_DATE('01-JAN-2023', 'DD-MON-YYYY') AS converted_date FROM DUAL; -- Converts string to date
17. TO_CHAR (Date): Converts a date to a string.
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') AS date_string FROM DUAL; -- Converts current date to string in 'YYYY-MM-DD' format
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') AS date_time_string FROM DUAL; -- Converts current date and time to string in 'DD-MON-YYYY HH24:MI:SS' format
18. TO_TIMESTAMP: Converts a string to a timestamp.
SELECT TO_TIMESTAMP('2023-01-01 12:34:56', 'YYYY-MM-DD HH24:MI:SS') AS converted_timestamp FROM DUAL; -- Converts string to timestamp
19. TO_TIMESTAMP_TZ: Converts a string to a timestamp with time zone.
SELECT TO_TIMESTAMP_TZ('2023-01-01 12:34:56 -05:00', 'YYYY-MM-DD HH24:MI:SS TZH:TZM') AS converted_timestamp_tz FROM DUAL; -- Converts string to timestamp with time zone
20. FROM_TZ: Converts a timestamp to a timestamp with time zone.
SELECT FROM_TZ(TIMESTAMP '2023-01-01 12:34:56', 'America/New_York') AS timestamp_with_tz FROM DUAL; -- Converts timestamp to timestamp with time zone
Tags:
Oracle