Week Start Date (Date return) of week and First & Last Date of last week

User_QVJMI

Respected Members,

Can someone please help me with below Query.

I want to find out

1- Week start date of any date I pass.

Input: 21-April-2021

Result: 18-April-2021

2- Last Week start date and end date of any date I pass.

Input: 21-April-2021

Result: 11-April-2021, 17-April-2021

Please help, TIA.

Gianni Ceresa

The ideal solution is to have a proper time dimension containing your own business rules for dates, because start and end date of a week is a something which must follow your own rules and is going to be different for other companies.

If you want to do that in OBIEE itself you can calculate things by using the date functions available: there is one returning the day of the week of a given date, take your business rules defining what is the start and end of a week and calculate it by using the TIMESTAMPADD function to add or subtract a given number of days from a date.

It's simple logic based on the day of the week most of the time.

User_QVJMI

Thank you Gianni for your kind response, I totally understand your point. Although I have found the solution to both question posting below for other fellow member.

1-

TIMESTAMPADD(SQL_TSI_DAY,- DAYOFWEEK(MIN(“Dim – Date”.” Date Column”)) + 1, MIN(“Dim – Date”.” Date Column”))

2-

"Your Times Table"."Your Date Column"

between

timestampadd(SQL_TSI_DAY, -DAYOFWEEK(current_date)-6, CURRENT_DATE)

and

timestampadd(SQL_TSI_DAY, -DAYOFWEEK(current_date), CURRENT_DATE)