date function

UDA
Enthusiast

date function

I need to come up with a date between for
prior week period of Friday through Thursday

Im normally a sql server guy some im learning the nuances. any help would be appreciate.
1 REPLY
Junior Contributor

Re: date function

This problem is typically solved doing a calculation based on a known date:

0001-01-05 was a friday (just don't ask why), thus (( x - DATE '0001-01-05') MOD 7) returns a value between 0 and 6 (0 representing friday).

SELECT calendar_date
FROM sys_calendar.calendar
WHERE calendar_date
BETWEEN CURRENT_DATE - 14 - ((CURRENT_DATE - DATE '0001-01-05') MOD 7)
AND CURRENT_DATE - 8 - ((CURRENT_DATE - DATE '0001-01-05') MOD 7)

Based on your definition of "prior week" you might have to modify that query a bit.

Dieter