Find last day of the previous week in Teradata?-CodePudding

I want to find out the previous weeks's last day in Teradata SQL using Sunday as the last day of the week. For example, today is Friday 1/27, so the last week ended on Sunday (1/22) and I would want to return 2023-01-22.

Other examples:

If current date is '2023-01-02', then the output I require is '2023-01-01' If current date is '2023-01-18', then the output I require is '2023-01-15'

With Hive query I would use:

date_sub(current_date, cast(date_format(current_date, 'u') as int));

What would the equivalent be in Teradata? I've tried using the code below but it seems to return the date of the closest Sunday instead of the date of the previous Sunday.

SELECT ROUND(current_date, 'd') (FORMAT 'yyyy-mm-dd');

CodePudding user response：

There are several ways:

Probably the best one is one of the built-in functions to return the previous xxxday <= the input date:

Td_Sunday(Current_Date - 1)

Or the function to return the next xxxday > input date:

Next_Day(Current_Date - 8, 'sun')

Truncating is least understandable:

Trunc(Current_Date, 'IW') -1

TRUNC supports three variations, only IW is usable, but restricted to Monday as week start:

IW: the Monday of the ISO week
WW: the same day of the week as January 1st of the year
W: the same day of the week as the first day of the month

CodePudding user response：

You can use the trunc function to return the first day of the a week, month, ect.

select trunc(current_date -7 ,'IW')

Current date today is 2023-01-27. This will return 2023-01-15, the previous Sunday.

EDIT: Sorry, meant to use the ISO week. As Dnoeth points out, the regular week option doesn't work consistently (which I didn't know, never used it for this before). Anyhoo, his answer is better than mine...