I have written below query which is working fine and its getting the data everytime for every previous one week.
select t.day_id
from F_TIME t
where t.day_id >= TO_NUMBER (TO_CHAR( TRUNC ( SYSDATE , ''IW'' ) - 7 , ''YYYYMMDD'' ))
and t.day_id < TO_NUMBER ( TO_CHAR ( TRUNC ( SYSDATE , ''IW'' ), ''YYYYMMDD'' ))
But now i want to rewrite this query in such a way that it will always get data starting from 1st of month till sysdate. So for example if i run this query now i should get data from 1st January 2018 till 11th January 2018.
If i run this query on 1st Febrauary then it will return data for entire month of January.I am not sure whether its possible to do in single query.
The dayid is number datatype and in format for e.g 20170815
To get the firts day of current month you can simply turn the first TRUNC(SYSDATE,'IW')
to TRUNC(SYSDATE,'MM')
and turn -7
to -1
if you want data from preceeding month if run on first day of current month.
Then you want to get data till sysdate, so again turn the second TRUNC(SYSDATE,'IW')
in TRUNC(SYSDATE)
:
SELECT t.day_id
FROM F_TIME t
WHERE t.day_id >= TO_NUMBER (TO_CHAR( TRUNC ( SYSDATE - 1 , 'MM' ) , 'YYYYMMDD' ))
AND t.day_id <= TO_NUMBER ( TO_CHAR ( TRUNC ( SYSDATE ), 'YYYYMMDD' ));
EDIT:
turned <
to <=
to get today.
in this way, if you run the query on 1st February you get entire January AND 1st February: if you want to exclude 1st February (i.e. today) you have to add a CASE WHEN
statement.