Hi, I am trying to get my head around case statements. Can someone please simplify in plain English the below statement; GETDATE() - CASE WHEN DATEPART(WEEKDAY,GETDATE()) = 2 THEN (4) ELSE (2) END Thanks.
This statement examines the value of the weekday of today's date and then subtracts 4 from today if today's weekday is 2 which means that it is Monday or subtracts 2 from today if today is any other day except Monday. In other words, if the day when the script is executed is Monday then it returns the day 4 days ago (Thursday of last week). Otherwise, the script returns the day two days ago. All of the above is valid with the default settings when the weeks begin on Sunday. The settings can be changed to, for example, consider Monday as the beginning of the week, in which case the script will return Friday of last week if executed on Tuesday and day 2 days ago on any other day. Generally speaking, it is not a good idea to use the math including both datetime and integer values together because it involves too many implicit conversions. It is better instead to use dateadd. For example, instead of using **getdate() - 2**, it is better to use **dateadd(day, -2, getdate())** Hope this helps. Oleg