Need to get Prior Sunday's date

[budguy]

Hi, new to teradata and working with date functions.
I need to get the prior sunday's date based on the current
date.

For example, for any date (mon-fri) would want the prior sunday's date. Current date = 2002-05-08 (yyyy-mm-dd)
from this would need a function to return 2002-05-05, prior sunday's date.


Not sure how to do this, have some examples using the
"add_months" fuction but not certain if I need to use that?


Any help would be much appreciated.

 

[RTwo]

This is a simple select statement which will help you to find the previous Sunday for today's date.

sel
CASE
when day_of_week=7 then calendar_date-6
when day_of_week=6 then calendar_date-5
when day_of_week=5 then calendar_date-4
when day_of_week=4 then calendar_date-3
when day_of_week=3 then calendar_date-2
when day_of_week=2 then calendar_date-1
when day_of_week=1 then calendar_date-7
END
as Sunday from sys_calendar.calendar where calendar_date = current_date;

You can build this logic into function by passing the current_date as a parameter. Hope it helps !