Weekend date for given a date

[AdityaK]

Is there a way to find Weekend date for a given date using a DB2
query? I see that there are scalar functions such as DAY, DAYOFMONTH,
DAYOFWEEK,etc are available...but couldn't find one to get a weekend
date for any given date. Any information is appreciated.

Thanks,
Aditya.

[Serge Rielau]

Not sure how you define weekenddate, but thsi litle function will give
you the last saturday or today if today is saturday:

create function saturday(d date)
returns date
contains sql deterministic no external action
return case when dayofweek(d)<7 then d - dayofweek(d) days else d end

Enjoys
Serge

[Hardy]

I think DAYOFWEEK function will help you, pls ref. to SQL Reference 1.

"AdityaK" <ad*******@gmail.com> ????
news:63**************************@posting.google.c om...

Is there a way to find Weekend date for a given date using a DB2
query? I see that there are scalar functions such as DAY, DAYOFMONTH,
DAYOFWEEK,etc are available...but couldn't find one to get a weekend
date for any given date. Any information is appreciated.

Thanks,
Aditya.

[Hardy]

my test:

db2 create table zd( time timestamp)
db2 insert into zd (values current timestamp)
db2 select date(time)+(7-dayofweek(time)) days from zd

pls try.

"AdityaK" <ad*******@gmail.com> ????
news:63**************************@posting.google.c om...

Is there a way to find Weekend date for a given date using a DB2
query? I see that there are scalar functions such as DAY, DAYOFMONTH,
DAYOFWEEK,etc are available...but couldn't find one to get a weekend
date for any given date. Any information is appreciated.

Thanks,
Aditya.

[Knut Stolze]

AdityaK wrote:

Is there a way to find Weekend date for a given date using a DB2
query? I see that there are scalar functions such as DAY, DAYOFMONTH,
DAYOFWEEK,etc are available...but couldn't find one to get a weekend
date for any given date. Any information is appreciated.

Do you want to have the Saturday or Sunday? If it is the Sunday, which one
do you want to get? (Note the convention in the US is often that the week
starts on Sunday, whereas it starts on Monday in Europe.)

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Tokunaga T.]

How about this?
------------------------------ Commands Entered ------------------------------
SELECT givendate
, givendate + (7-DayOfWeek_ISO(givendate)) DAYS AS Weekend
FROM (VALUES Current Date) AS R(givendate)
;
------------------------------------------------------------------------------

GIVENDATE WEEKEND
---------- ----------
2004-10-27 2004-10-31

1 record(s) selected.

[AdityaK]

to*****@jp.ibm.com (Tokunaga T.) wrote in message news:<81**************************@posting.google. com>...

How about this?
------------------------------ Commands Entered ------------------------------
SELECT givendate
, givendate + (7-DayOfWeek_ISO(givendate)) DAYS AS Weekend
FROM (VALUES Current Date) AS R(givendate)
;
------------------------------------------------------------------------------

GIVENDATE WEEKEND
---------- ----------
2004-10-27 2004-10-31

1 record(s) selected.

Thanks a lot everybody for your response...
I was trying out this query to get SATURDAY's date in that week.
"select column1,column2,rcpt_date,weekenddate(rcpt_date) from ATABLE
where conditions;"
I tried the query in previous response but am getting SQL error:
'SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD CURRENT, TOKEN FULL
LEFT INNER RIGHT WAS EXPECTED'

Thanks,
Aditya K

[AdityaK]

to*****@jp.ibm.com (Tokunaga T.) wrote in message news:<81**************************@posting.google. com>...

How about this?
------------------------------ Commands Entered ------------------------------
SELECT givendate
, givendate + (7-DayOfWeek_ISO(givendate)) DAYS AS Weekend
FROM (VALUES Current Date) AS R(givendate)
;
------------------------------------------------------------------------------

GIVENDATE WEEKEND
---------- ----------
2004-10-27 2004-10-31

1 record(s) selected.

Thanks a lot... I was able to get Weekend date (Saturday's date) using
above query....