Query to 'get the records from the actual month' doesn't get me the records that have a bigger day that the current one

Question

I have the following query to get the records from a table from the current month

select *
from 
   myTable 
where 
   my_date BETWEEN trunc (sysdate, 'mm') AND SYSDATE;

This query works if the records have a lower day compared to the current one example: if today is 27/10/2016 and I have a record that have this date: 28/10/2016 The record with date 28/10/2016 is not showing

I insert the records using this format TO_DATE( '28/10/2016 18:02:44', 'dd/mm/yyyy hh24:mi:ss')

I want to show all the records from the curren month even if the day is bigger than the actual date

Do you need all the dates >= than the first day of the current month? — Aleksej
I just want the records that have a date in the current month — Bill_Data23

JohnHC JohnHC · Accepted Answer · 2016-10-27T14:31:35

Either:

select *
from 
   myTable 
where 
   my_date BETWEEN trunc (sysdate, 'mm') AND add_months(trunc (sysdate, 'mm'),1)- 1/(24*3600)

or

select *
from 
   myTable 
where 
   trunc(my_date,'mm') = trunc (sysdate, 'mm')

The first is sargable, the second is more readable.

Query to 'get the records from the actual month' doesn't get me the records that have a bigger day that the current one

2 Answers