How Can I get the 1st Day of the Week

[Anonymous]

Hi!  I have a  "Snapshot_Date" field that's based on the daily activity dates and  "Snapshot Week" field that is based on the Monday week date of theSnapshot Date   field.

Snapshot Week                 Snapshot Date

06/23/2014                        06/22/2014

06/23/2014                        06/23/2014

06/23/2014                        06/24/2014

06/23/2014                         06/25/2014

06/23/2014                        06/26/2014

06/23/2014                         06/27/2014

06/23/2014                        06/28/2014

06/30/214                          06/29/2014

06/30/2014                        07/01/2014

06/30/2014                        07/02/2014

06/30/2014                         07/03/2014

06/30/2014                          07/04/2014

How can I compose my where clause where I  get all the Mon day Dates or the 1st day date  of the week (Sun or Tue)  if  Monday date is not available in the snaphot Date. ?  In my sample above I'm trying to get only 1 record per week...Hence, to  extract only   the 06/23  MOn and 06/29 Sun since  MOnday date 06/30  for the week of 06/30 is not available...

right now my where clause formula is

Snapshot_Week = Snapshot_Date      but I could not get the records for a certain week  if there is no MOnday date in the Snapshot_Date  .

Tks.

[avinashelite]

Hi Cacosta,

Please use weekstart() function. It Returns a value corresponding to a timestamp with the first millisecond of the first date ( Monday ) of the calendar week containing date. The default output format will be the DateFormat set in the script. Shift is an integer, where the value 0 indicates the week which contains date. Negative values in shift indicate preceding weeks and positive values indicate succeeding weeks. If you want to work with weeks not starting midnight between Sunday and Monday, indicate an offset in days in weekoffset. This may be given as a real number indicating days and/or fractions of a day.

eg:

weekstart ( '2006-01-12' ) returns '2006-01-09' with an underlying numeric value corresponding to '2006-01-09 00:00:00.000'

weekstart ( '2006-01-12', -1 ) returns '2006-01-02' with an underlying numeric value corresponding to '2006-01-02 00:00:00.000'

weekstart ( '2006-01-12', 0, 1 ) returns '2006-01-10' with an underlying numeric value corresponding to '2006-01-10 00:00:00.000'

Regards,

@vi