Last Weekday n In T-SQL

T-SQL query:

SELECT DATEADD(D, n-DATEPART(DW, GETDATE()), DATEDIFF(DD, 0, GETDATE()))

can be used to get last weekday n in T-SQL. Following is the value of parameter n:

At Least Hours Ago	Mon	Tue	Wed	Thu	Fri	Sat	Sun
0	2	3	-3	-2	-1	0	1
24	2	-4	-3	-2	-1	0	1
48	-5	-4	-3	-2	-1	0	1

The result is that weekday at 0:00:00 timestamp.

However, if this is to be used as parameter in SSRS, it would be:

=DATEADD("D", n-WEEKDAY(TODAY())), TODAY())

This is because the syntax are different in parameter of SSRS and T-SQL.

Many people would prefer predict in T-SQL as follows:

WHERE PERIOD >= STARTDATE AND PERIOD < ENDDATE

However, in day-to-day life, people prefer both dates be to inclusive. In this case, the predict should be:

WHERE PERIOD >= STARTDATE AND PERIOD <= ENDDATE

Therefore, the n parameter should be changed accordingly.


http://weblogs.sqlteam.com/jhermiz/archive/2007/08/15/60289.aspx