How to go last week of end date ?

Database

How to go last week of end date ?

Hi,

I need to pull the last week end date where my week stat day is 'monday' and END day is 'Sunday'. When i am running below query it is working fine for all days except for Monday. Please find the results. I am using TD 12.0.0.03 ver

select distinct date '2013-12-23'-dayofweek(date '2013-12-23'-2)-1 from fact;

ans : 12/15/2013

select distinct date '2013-12-24'-dayofweek(date '2013-12-24'-2)-1 from fact;

12/22/2013

select distinct date '2013-12-25'-dayofweek(date '2013-12-25'-2)-1 from fact;

12/22/2013

12/22/2013 is the last week end date .

Please help me to right a query which will works for all the days to go last week end.

Thanks in advance !

1 REPLY
Junior Contributor

Re: How to go last week of end date ?

Did you reading/answer the old post instead of opening a new one?

Why do you use a very old version, 12.0.0.03, which is definitely no production system?

dayofweek is based on a week starting on sunday not monday, you have to adjust your logic based on this (i already posted a SQL UDF with a calculation to get a begin of a week based on ISO).

As i wrote, since 13.10 you could easily do a SQL UDF and for 14 there's a DAYNUMBER_OF_WEEK:

SELECT current_date as d, 
d - DAYNUMBER_OF_WEEK(d,'iso'),
d - d-((dayofweek(d) + 5) MOD 7 + 1)