Bill_Data23 Bill_Data23 - 1 month ago 6
SQL Question

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

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

Answer

Either:

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

or

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

The first is sargable, the second is more readable.