Oracle SQL Basic Day 6
Today we are going to cover oracle SQL Date related function. The default date format is DD-MON-YYYY e.g 01-JAN-2025
This could be different based on configuration done by DBA.
These function will help you to find current system date, convert date to varchar and vise versa. Find difference between two dates, find future date or future day.
most of these function work with date data type. There is another data time named as TIMESTAMP in oracle database which support full date with time stamp.
Let's learn them and practice as well. Make sure to run these queries to get better understanding and clear the concepts.
- SYSDATE
- ADD_MONTHS
- MONTH_BETWEEN
- NEXT_DAY
- LAST_DAY
- TO_DATE
- TO_CHAR
1. SYSDATE() – Returns the Current System Date and Time
SELECT SYSDATE AS CURRENT_DATE FROM DUAL;
SELECT FIRST_NAME, HIRE_DATE, SYSDATE - HIRE_DATE AS DAYS_WORKED
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 90;
- The above query shows the current date for the first 5 employees.
- The second calculates how many days an employee has worked.
- SYSDATE is useful for comparing dates with the current timestamp.
2. ADD_MONTHS() – Add or Subtract Months from a Date
SELECT FIRST_NAME, HIRE_DATE, ADD_MONTHS(HIRE_DATE, 6) AS REVIEW_DATE
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 80;
SELECT FIRST_NAME, ADD_MONTHS(SYSDATE, -12) AS ONE_YEAR_AGO
FROM EMPLOYEES
WHERE ROWNUM <= 3;
- Adds 6 months to hire dates to set a review date.
- Calculates the date exactly one year ago from today.
- Great for probation periods, anniversaries, or forecasting.
3. MONTHS_BETWEEN() – Calculates Number of Months Between Two Dates
SELECT FIRST_NAME, MONTHS_BETWEEN(SYSDATE, HIRE_DATE) AS MONTHS_WORKED
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100;
SELECT FIRST_NAME, MONTHS_BETWEEN(SYSDATE, ADD_MONTHS(HIRE_DATE, 12)) AS TIME_TILL_ANNIV
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 60;
- Shows how many months an employee has worked.
- Calculates time left until their 1-year work anniversary.
- Useful for tenure calculations and workforce planning.
4. NEXT_DAY() – Returns Next Specified Day After a Date
SELECT FIRST_NAME, HIRE_DATE, NEXT_DAY(HIRE_DATE, 'FRIDAY') AS NEXT_FRIDAY
FROM EMPLOYEES
WHERE ROWNUM <= 5;
SELECT FIRST_NAME, NEXT_DAY(SYSDATE, 'MONDAY') AS UPCOMING_MONDAY
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 110;
- Finds the next Friday after each employee’s hire date.
- Gets the date of the next Monday from today.
- Helpful for scheduling events or setting review dates.
5. LAST_DAY() – Returns the Last Day of the Month for a Given Date
SELECT FIRST_NAME, HIRE_DATE, LAST_DAY(HIRE_DATE) AS END_OF_MONTH
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 50;
SELECT FIRST_NAME, LAST_DAY(SYSDATE) AS MONTH_END
FROM EMPLOYEES
WHERE ROWNUM <= 3;
- Determines the last day of the month when an employee was hired.
- Shows the last day of the current month.
- Perfect for monthly reporting and payroll cutoffs.
6. EXTRACT() – Extracts a Specific Part from a Date
SELECT FIRST_NAME, HIRE_DATE, EXTRACT(YEAR FROM HIRE_DATE) AS HIRE_YEAR
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 30;
SELECT FIRST_NAME, EXTRACT(MONTH FROM HIRE_DATE) AS HIRE_MONTH
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 60;
- Extracts the hire year of each employee.
- Extracts the hire month for analysis by season or quarter.
- Ideal for grouping data by year, month, or day.
7. TRUNC() – Truncates Time from Date (or to Specific Date Parts)
SELECT FIRST_NAME, HIRE_DATE, TRUNC(HIRE_DATE) AS TRUNCATED_DATE
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 70;
SELECT FIRST_NAME, TRUNC(SYSDATE, 'MM') AS START_OF_MONTH
FROM EMPLOYEES
WHERE ROWNUM <= 3;
- Removes the time component from a date, returning just the date.
- Truncates the current date to the first day of the month.
- Helps normalize dates for grouping and comparisons.
Example 2: Calculate the date 7 days from today
SELECT SYSDATE + 7 AS next_week FROM dual;
Adds 7 days to the current date, useful for deadline or future date calculations.
2. TO_DATE
Description:
TO_DATE converts a string into an Oracle DATE format, enabling date comparisons and operations with string inputs.
Example 1: Convert a string to a date
SELECT TO_DATE('2025-06-01', 'YYYY-MM-DD') AS converted_date FROM dual;
Transforms the string '2025-06-01' into an Oracle date value.
Example 2: Find records after a specific date
SELECT employee_id, hire_date
FROM employees
WHERE hire_date > TO_DATE('2024-01-01', 'YYYY-MM-DD');
Retrieves employees hired after January 1, 2024.
3. TO_CHAR
Description:
TO_CHAR converts a date or timestamp to a formatted string, allowing easy display in custom formats.
Example 1: Format the current date as 'DD-MON-YYYY'
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY') AS formatted_date FROM dual;
Displays today’s date in a human-readable format like '27-MAY-2025'.
Example 2: Extract month name from a date
SELECT TO_CHAR(hire_date, 'Month') AS hire_month FROM employees;
Returns the month name of the employee's hire date.
4. ADD_MONTHS
Description:
ADD_MONTHS adds a specified number of months to a date, useful for calculating expiry dates or recurring billing cycles.
Example 1: Add 3 months to a contract start date
SELECT contract_start, ADD_MONTHS(contract_start, 3) AS contract_end
FROM contracts;
Calculates the contract end date by adding 3 months to the start date.
Example 2: Subtract 6 months from today's date
SELECT SYSDATE, ADD_MONTHS(SYSDATE, -6) AS six_months_ago FROM dual;
Finds the date exactly six months before today.
5. MONTHS_BETWEEN
Description:
MONTHS_BETWEEN returns the number of months between two dates, useful for duration and aging calculations.
Below are some more example of queries with description to understand and clear the concept of oracle SQL date related function. You can run them and understand it based on description provided. It gave practical knowledge based on real world request or problems.
Example 1: Calculate months between hire date and today
SELECT employee_id, MONTHS_BETWEEN(SYSDATE, hire_date) AS months_worked
FROM employees;
Shows how many months each employee has worked.
Example 2: Months between two specific dates
SELECT MONTHS_BETWEEN(TO_DATE('2025-12-31', 'YYYY-MM-DD'), TO_DATE('2025-01-01', 'YYYY-MM-DD')) AS months_diff FROM dual;
Returns 11, the number of months between January 1 and December 31, 2025.
2. Calculate the day of the week for each employee’s hire date
SELECT employee_id, hire_date, TO_CHAR(hire_date, 'Day') AS day_of_week
FROM employees;
Shows the day of the week (e.g., Monday, Tuesday) when each employee was hired.
---
3. List employees whose birthdays are in the current month
SELECT employee_id, first_name, TO_CHAR(birth_date, 'Month') AS birth_month
FROM employees
WHERE TO_CHAR(birth_date, 'MM') = TO_CHAR(SYSDATE, 'MM');
Finds employees having birthdays in the current month.
---
4. Find employees with more than 5 years of service
SELECT employee_id, first_name, hire_date, FLOOR(MONTHS_BETWEEN(SYSDATE, hire_date)/12) AS years_of_service
FROM employees
WHERE MONTHS_BETWEEN(SYSDATE, hire_date) > 60;
Lists employees who have worked for over 5 years.
---
5. Show last day of the month for each employee’s hire date
SELECT employee_id, hire_date, LAST_DAY(hire_date) AS last_day_of_hire_month
FROM employees;
Displays the last date of the month corresponding to each employee's hire date.
---
6. Find employees hired on a weekend (Saturday or Sunday)
SELECT employee_id, hire_date
FROM employees
WHERE TO_CHAR(hire_date, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') IN ('SAT', 'SUN');
Returns employees hired on weekends.
Hopefully you learn something from this lesson. Will continue for next lesson and cover basic of oracle SQL sub queries.
0 Comments