How to find Oracle SQL First Day of Month

To retrieve the Oracle SQL first day of Month, you can use the TRUNC function. There are specific parameters to set on TRUNCT to retrieve it. I would like to show 10 different examples of how to get the first day of the month like below.

1. Current Month
2. Specified Date with TO_DATE
3. with SYSDATE
4. Previous Month
5. Next Month
6. Using ADD_MONTHS
7. Using EXTRACT
8. Using FIRST_DAY Function
9. Using DATE Arithmetic
10. Using TRUNC with MONTH

Here we use the TRUNC function lot of places there for this is a small explanation of the TRUNC function. According to the Oracle, we can pass the different units of measurement to the TRUNC function and retrieve different dates like below. But in this article, we are focusing on Oracle SQL first day of month.

Oracle SQL Example

WITH dates AS (   
  SELECT date'2023-01-01' d FROM dual union   
  SELECT date'2023-01-10' d FROM dual union   
  SELECT date'2023-02-01' d FROM dual union   
  SELECT timestamp'2023-03-03 23:44:32' d FROM dual union   
  SELECT timestamp'2023-04-11 12:34:56' d FROM dual    
)   
SELECT d "Original Date",   
       trunc(d) "Date, time removed",   
       to_char(trunc(d, 'mi'), 'dd-mon-yyyy hh24:mi') "Nearest Minute",   
       trunc(d, 'iw') "Start of Week",   
       trunc(d, 'mm') "Start of Month",   
       trunc(d, 'year') "Start of Year"   
FROM dates;

1: Current Month

--
SELECT TRUNC(SYSDATE, 'MM') AS first_day_of_month FROM dual; 
Return
------------
2/1/2024
--

Explanation: As we discussed earlier this query uses the TRUNC function to truncate and MM is used as the unit of measurement of TRUNC then returns the first day of the month. There are some other Units of measurement for a month like below.

• MONTH
• MON
• MM
• RM

2: Specific Date

--
SELECT TRUNC(TO_DATE('2023-07-15', 'YYYY-MM-DD'), 'MM') AS first_day_of_month FROM dual;
--

Explanation: If you want to take the first day of a given date then we can use String to Date convert first and then use TRUNC to retrieve first day of month.

3: Using SYSDATE

--
SELECT TRUNC(SYSDATE, 'MM') AS first_day_of_current_month FROM dual; 
--

Explanation: SYSDATE returns the operating system date or Current date

4: Previous Month

--
SELECT TRUNC(ADD_MONTHS(SYSDATE, -1), 'MM') AS first_day_of_previous_month FROM dual; 
Return
----------
1/1/2024
--

Explanation: The ADD_MONTHS  function is used to add or reduce months from a given date. This case has to reduce the month by 1 to take the previous month. This query calculates the first day of the previous month using the current date (SYSDATE).

5: Next Month

--
SELECT TRUNC(ADD_MONTHS(SYSDATE, 1), 'MM') AS first_day_of_next_month FROM dual; 
--

Explanation: Similar to Example 4, ADD_MONTHS is used for take next month.

6: Using MONTHS_BETWEEN

--
SELECT ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), 1) AS first_day_of_january_next_year FROM dual; 
--

Explanation: Here what happens is query retrieves the first day of January of the next year and then adds one month.

7: Using EXTRACT

--
SELECT TO_DATE(EXTRACT(YEAR FROM SYSDATE) || '-01-01', 'YYYY-MM-DD') AS first_day_of_current_year FROM dual; 
--

Explanation: Extract use to fetch Year from Sysdate and concat with hardcoded value.

8: Using FIRST_DAY Function

--
SELECT FIRST_DAY(TO_DATE('2023-09-15', 'YYYY-MM-DD')) AS first_day_of_month FROM dual; 
--

Explanation: The FIRST_DAY function returns the first day of the month for the specified date.

9: Using DATE Arithmetic

--
SELECT TRUNC(SYSDATE, 'YYYY') + INTERVAL '1' MONTH AS first_day_of_january_next_year FROM dual; 
--

Explanation: This query adds one month to the truncated current date to obtain the first day of January of the next year.

10: Using TRUNC with MONTH

--
SELECT TRUNC(SYSDATE, 'MONTH') AS first_day_of_month FROM dual; 
--

Explanation: This query truncates the current date (SYSDATE) to the first day of the month using the 'MONTH' format.

TRUNC function also can be used to compare dates. Above are some examples of how-to Oracle SQL first day of month. You can use whatever way according to your requirements.

If you want to use one of the options in the WHERE condition, there can be performance problems due to Oracle not being able to use an index on such a condition in the where clause. But that kind of situation can be handled by adding a Function base index on a table. This is an additional thing for you if you are dealing with a huge amount of data.