If Statement in my where clause with dates-CodePudding

for whatever reason, I can't use weekday functions in SQL at my organization, so am needing some help with a workaround. I have a SQL code which is supposed to query the previous weekday. So this is what we currently use:

select p.businessDate, p.accountNumber (more fields, but unecessary)
from Table p, 
where p.businessDate = dateadd(DAY, -1, today())

The problem is on Mondays, this query pulls data from Sunday, when we need it for Friday. Is there a way to get around using dateadd(WEEKDAY, -1, today())? This code is run automatically, Mon-Fri, daily.

CodePudding user response：

I found something here that I think can help you.

If you can use DATENAME then you can check if the name of the current date is 'Monday'. If it is, then instead of dateadd(DAY, -1, today()) you can do dateadd(DAY, -3, today()).

CodePudding user response：

Since you said you can use DATEPART, the case statement checks if the current day is Monday, if it is, it subtracts 3 days to get Friday, else it subtracts 1 day, DATEPART returns a numeric value starting with 1 for Sunday and moves to 7 for Saturday, so as Monday is the date of concern we check for the value of 2, this is SQL Server syntax.

select p.businessDate, p.accountNumber (more fields, but unecessary)
from Table p, 
where businessDate = CASE WHEN DATEPART(WEEKDAY, GETDATE()) = 2 
THEN DATEADD(DAY, -3, GETDATE()) 
ELSE DATEADD(DAY, -1, GETDATE()) END

DATEPART documentation - datepart-transact-sql