Oracle - trunc(DateTime) between trunc(Sysdate - 104) and trunc(Sysdate - 75)

Question

I want to select few data from a Oracle table of June month(June 1st to June 30th of 2017) and the start date will be from tomorrow(Sep 13th). Hence I wrote a query in below format,

select * from table where column1='Data1' and Column2='Data2'
and trunc(DateTime) between trunc(sysdate-104) and trunc(sysdate-75)

I'm not able to check this query as I don't have tool for this. I just wrote it in notepad and want to share to my friend.

My Question - Will trunc(DateTime) between trunc(sysdate-104) and trunc(sysdate-75) condition will give data between June1st to June31 or Does any Syntax issue there?

Answer 1

There is no problem with the syntax itself even though your formulation is time sensitive, which means that tomorrow it won't return the same result.

Instead, go with something like this :

AND TRUNC(DateTime) BETWEEN to_date('2016-06-01','YYYY-MM-DD')  
                        AND to_date('2016-06-30','YYYY-MM-DD') 

Answer 2

Hi I think the most accurate would be:

select * from table where column1='Data1' and Column2='Data2'
    AND DateTime BETWEEN TRUNC(sysdate-104) 
                 AND TRUNC(sysdate-75) 
                 + interval '23' hour 
                 + interval '59' minute 
                 + interval '59' second;

Answer 3

Just cast the date format to month/year and compare against that.

select *
from table
where column1 = 'Data1'
and column2 = 'Data2'
and to_char(DateTime, 'MMYYYY') = '062017';