4

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.

mustaccio
  • 18,234
  • 16
  • 48
  • 57
shecode
  • 1,716
  • 6
  • 32
  • 50

1 Answers1

10

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

data_henrik
  • 16,724
  • 2
  • 28
  • 49