Oracle

In Oracle, DATE functions are essential for handling date and time data types. They allow you to manipulate and format dates for various applications, such as calculating age, determining the difference between dates, or formatting dates for display. Understanding these functions is crucial for effective data querying and reporting.

1. Common DATE Functions

   • SYSDATE: Returns the current date and time.
   • CURRENT_DATE: Returns the current date in the session's time zone.
   • ADD_MONTHS(date, n): Adds a specified number of months to a date.
   • LAST_DAY(date): Returns the last day of the month for the specified date.

2. Date Formatting Functions

   • TO_CHAR(date, format): Converts a date to a string using the specified format.

     SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') AS formatted_date FROM dual;
     

   • TO_DATE(string, format): Converts a string to a date using the specified format.

     SELECT TO_DATE('2024-09-19', 'YYYY-MM-DD') AS date_value FROM dual;
     

3. Date Arithmetic

   • Subtracting Dates: You can subtract one date from another to get the difference in days.

     SELECT (SYSDATE - TO_DATE('2024-01-01', 'YYYY-MM-DD')) AS days_difference FROM dual;
     

   • Using DATE Functions with Tables:

     SELECT author_name,
            TO_CHAR(birth_date, 'MM-DD-YYYY') AS formatted_birth_date
     FROM authors
     WHERE birth_date > ADD_MONTHS(SYSDATE, -12);
     

4. Best Practices

   • Always use the correct format when converting strings to dates to avoid errors.
   • Use DATE functions in WHERE clauses to filter records effectively.
   • Test your queries with sample data to ensure the logic works as expected.

By mastering these DATE functions, you can enhance your ability to query and manipulate date data in Oracle databases effectively.

To gain complete access, login with gmail or outlook, no need of signup. click here

SELECT EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date)
    COUNT(*) AS monthly_order_count
FROM act_order
GROUP BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date);

SELECT EXTRACT( HOUR FROM order_date)
    COUNT(*) AS order_count
FROM act_order
GROUP BY EXTRACT( HOUR FROM order_date)
ORDER BY COUNT(*) DESC
FETCH FIRST 5 ROWS ONLY;

SELECT EXTRACT(YEAR FROM SYSDATE) - birth_year AS age
    COUNT(*) AS customer_count
FROM org_client
WHERE gender= 'M'
GROUP BY EXTRACT(YEAR FROM SYSDATE) - birth_year
ORDER BY EXTRACT(YEAR FROM SYSDATE) - birth_year DESC
FETCH FIRST 3 ROWS ONLY;