SQL server: How do i get the date of previous Saturday

Question

I need to filter a table from last Saturday 7pm to now. How do i do that?

The column i'm filtering is of dateTime2(0) format?

Answer 1

This expression will always give you the previous Saturday at 7pm, independent of your DATEFIRST setting:

select DATEADD(week,
       DATEDIFF(week,'19000101',CURRENT_TIMESTAMP),'1899-12-30T19:00:00')

If it's run on a Saturday, it'll give you the previous Saturday.

Answer 2

SELECT * From TableName Where MyDate >= DATEADD(HH,19, DATEADD(DAY, (DATEDIFF(DAY, '19000106', GetDate()) - 1) / 7 * 7, '19000106'))