Weekend date for given a date

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

AdityaK - 26 Oct 2004 23:37 GMT

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.

Reply to this Message

Serge Rielau - 27 Oct 2004 01:24 GMT

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

Reply to this Message

Hardy - 27 Oct 2004 03:50 GMT

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

> 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,
[quoted text clipped - 3 lines]
> Thanks,
> Aditya.

Reply to this Message

Hardy - 27 Oct 2004 04:08 GMT

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.

> 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,
[quoted text clipped - 3 lines]
> Thanks,
> Aditya.

Reply to this Message

Knut Stolze - 27 Oct 2004 07:03 GMT

> 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.)

Signature

Knut Stolze
Information Integration
IBM Germany / University of Jena

Reply to this Message

Tokunaga T. - 27 Oct 2004 08:47 GMT

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.

Reply to this Message

AdityaK - 27 Oct 2004 16:38 GMT

> How about this?
> ------------------------------ Commands Entered ------------------------------
[quoted text clipped - 9 lines]
>
> 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

Reply to this Message

AdityaK - 27 Oct 2004 16:58 GMT

> How about this?
> ------------------------------ Commands Entered ------------------------------
[quoted text clipped - 9 lines]
>
> 1 record(s) selected.

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

Reply to this Message