CODEWITHSUNDEEP
datedb2week-number
shecode
shecode

Reputation: 1726

How to get week beginning date in DB2?

Using DB2, without a calendar lookup, How do I determine the Sunday of the week to which a certain date belongs? For example, give a set of dates:

date
----------
2015-05-01
2015-05-02
2015-05-03
2015-05-04
2015-05-05
2015-05-06
2015-05-07

how do I find the date corresponding to the beginning of the week for each date, i.e. if it's Sunday it's '2015-04-26'

date        wc
----------  ----------
2015-05-01  2015-04-26
2015-05-02  2015-04-26
2015-05-03  2015-05-03
2015-05-04  2015-05-03
2015-05-05  2015-05-03
2015-05-06  2015-05-03
2015-05-07  2015-05-03

I've found many solutions to find "week number" but I need this as a date data type.

Upvotes: 4

Views: 10222

Answers (1)

data_henrik
data_henrik

Reputation: 17156

Something like ((current date) - (dayofweek(current date)-1) days) does the trick. The expression returns the Sunday of the current week.

DAYOFWEEK is a function to return the day for a given date or timestamp with Sunday being "1".

Upvotes: 10

Related Questions