CODEWITHSUNDEEP
oracle-database
Raymond
Raymond

Reputation: 103

Condition Check with specific week start day

I'm trying to check whether a particular day is present in currentweek. But I would prefer for my week to be from Sunday to Saturday. As of now week is from Monday to Sunday. This is my query:

SELECT count(d3.COL02) ,d3.COL01 from Table  d3 where TO_CHAR(d3.COL10, 'YYYYWW') = TO_CHAR(SYSDATE, 'YYYYWW');

I'm new to oracle, so could somebody help with this?

Say

TableA contains following data:
ID,COL01,COL02,COL10
1,199,10,28-07-18
2,199,10,29-07-18
3,199,20,30-07-18
4,199,20,31-07-18

exceuting query SELECT count(d3.COL02) ,d3.COL01 from TableA d3 where TO_CHAR(d3.COL10, 'YYYYWW') = TO_CHAR(SYSDATE, 'YYYYWW') group by d3.COL01;

gives output

count(COL02),COL01
2,199

But expecting output:

count(COL02),COL01
4,199

Upvotes: 0

Views: 48

Answers (2)

Ponder Stibbons
Ponder Stibbons

Reputation: 14858

Look at this query:

with tableA(id, col01, col02, col10) as (
    select 1, 199, 10, date '2018-07-28' from dual union all
    select 2, 199, 10, date '2018-07-29' from dual union all
    select 3, 199, 20, date '2018-07-30' from dual union all
    select 4, 199, 20, date '2018-07-31' from dual )
select d3.col10, 
       to_char(col10, 'day', 'nls_date_language=English') day, 
       to_char(col10, 'yyyyww') yw,
       to_char(col10 + 1, 'yyyyww') yw1
  from tableA d3

Result:

COL10       DAY       YW     YW1
----------- --------- ------ ------
2018-07-28  saturday  201830 201830
2018-07-29  sunday    201830 201831
2018-07-30  monday    201831 201831
2018-07-31  tuesday   201831 201831

Column YW is what you have now, when you add one day to date you assign Sunday to next week. So change your condition to:

where to_char(d3.col10 + 1, 'yyyyww') = to_char(sysdate + 1, 'yyyyww')

Upvotes: 2

Nikhil
Nikhil

Reputation: 3950

this will work :

select  count(*)from TableA d3 where to_char(d3.col10,'IW')=to_char(sysdate,'IW');

and the output will be 2 itself because 28 and 29 july comes in previous months.

Upvotes: 0

Related Questions