MVSFORUMS.com

vini

Hi,
Does anybody have a one-pass solution without programming a whole lot around it to get the previous Saturday date of the week given any date as input.

For example if the Input is TODAYS DATE : 03-13-06 or tomorrows or day after tomorrow date the Output still should be 03-11-06 (last saturday date).

Thnks.
Vini

kolusu · Posted: Mon Mar 13, 2006 7:03 pm Post subject:

vini,

Try this untested sql

with current date

vini · Posted: Tue Mar 14, 2006 11:31 am Post subject:

Awesome !!! Tested for several dates and it works fine Smile

Kolusu,

Can you explain how it evaluates/works ? Thnks.

Confused

kolusu · Posted: Tue Mar 14, 2006 11:46 am Post subject:

vini · Posted: Tue Mar 14, 2006 1:21 pm Post subject:

Kolusu,

I am confused because when I brake up the suggested SQL to understand it ...as follows..I get a -171 Sql Cd error .

SELECT DATE(CURRENT DATE) - DAYOFWEEK(CURRENT DATE) From SysIbm.Sysdummy1.

Hence, I do not understand how appending a DAYS clause to the above does it all ?

I have used the DAYOFWEEK before so that was not the source of confusion. Neutral

I dont really need to understand to make it work , that I have tested and it sure does , but when we have a formal code review I could be questioned and I just wanted to be able to handle peer review questions.

Thanks
Vini

kolusu · Posted: Tue Mar 14, 2006 1:38 pm Post subject:

vini · Posted: Tue Mar 14, 2006 2:39 pm Post subject:

Kolusu,
If I was the kinds to code without understanding I wouldnt have bothered to ask for an explanation behind the logic. Rest assured I will understand it sooner or later..there still is time before the code moves to prodn.

Although I still do not understand because what you are doing is subtracting a date from itself (in days) and arriving at a previous saturday date.
I realise the mistake I made in the break up SQL and the following gets the same results ..as am operating on 2 dates of same format now:
SELECT DATE(CURRENT DATE) - DATE(DAYOFWEEK(CURRENT DATE))
FROM SYSIBM.SYSDUMMY1;

What if one needs to get a WEDNESDAY day instead of SATURDAY ..using the same logic ...how would this simple logic work that out ? Perhaps if there are 2 SQL to compare it may be easier to understand.

Can anyone else chime in .. with an alternate view point or reasoning ..if you have one ? I maybe really dumb to be still asking but am not pretending here.

Thanks.

Bithead · Posted: Tue Mar 14, 2006 3:05 pm Post subject:

This is how this works:

SELECT DATE(CURRENT DATE) - DAYOFWEEK(CURRENT DATE) DAYS

If today is a Sunday, then DAYOFWEEK(CURRENT DATE) returns a value of 1 so

SELECT DATE(CURRENT DATE) - 1 DAYS is yesterday which is Saturday.

If today is a Monday, then DAYOFWEEK(CURRENT DATE) returns a value of 2 so

SELECT DATE(CURRENT DATE) - 2 DAYS is 2 days ago which is Saturday.

If today is a Wednesday, then DAYOFWEEK(CURRENT DATE) returns a value of 3 so

SELECT DATE(CURRENT DATE) - 2 DAYS is 3 days ago which is Saturday.

and so on up to

If today is a Saturday, then DAYOFWEEK(CURRENT DATE) returns a value of 7 so

SELECT DATE(CURRENT DATE) - 7 DAYS is a week ago which is last Saturday.

SureshKumar · Intermediate Joined: 23 Jan 2003 Posts: 211 Topics: 21

vini,

kolusu · Posted: Tue Mar 14, 2006 3:25 pm Post subject:

Bithead · Posted: Tue Mar 14, 2006 4:36 pm Post subject:

This seems to work to get last wednesday's date though I have not fully tested it:

vini · Posted: Wed Mar 15, 2006 9:33 am Post subject:

Thanks Bithead , It all makes sense now and your last SQL sure does work too Very Happy