HELP:Bods logic which will generate start date and end date based on week

[Praneetha]

I am working on bods logic which will generate start date and end date based on week start ( monday) and week end ( sunday).

For example if I am running my job on 05/07/2024 ( sysdate)

Start date: 01/07/2024

End date: 07/07/2024

I am using day_of_week and based on that am adding and subtracting integer to get start and end dates, but I have doubt if logic works for last week of month and number of days in a month.

Can anyone help on this logic please?

#sap dataservices

#sap bods

[Julian_Riegel · Accepted solution]

Hi,

I have done that in the past and just copied it out of my code.

I am pretty sure that this is working as you are requesting. Please still do a double check since this implementation is quite a while back.

To get start date:

to_date(
  to_char(
    sysdate() + num_to_interval(
      decode(
        day_in_week(sysdate()) = 1,
        0,
        day_in_week(sysdate()) >= 2,
        1 - day_in_week(sysdate()),
        1
      ),
      'D'
    ),
    'YYYY-MM-DD'
  ),
  'YYYY-MM-DD'
)

To get end date:

to_date(
  to_char(
    to_date(
      to_char(
        sysdate() + num_to_interval(
          decode(
            day_in_week(sysdate()) = 1,
            0,
            day_in_week(sysdate()) >= 2,
            1 - day_in_week(sysdate()),
            1
          ),
          'D'
        ),
        'YYYY-MM-DD'
      ),
      'YYYY-MM-DD'
    ) + num_to_interval(6, 'D'),
    'YYYY-MM-DD'
  ),
  'YYYY-MM-DD'
)

You could also use a script to store start date upfront and then calculate the end date from it since end date has the start date code embedded, so it is not the nicest.

However, please check and let me know if this works for you.

Julian