Go Premium for a chance to win a PS4. Enter to Win

x
Solved

# SQL Use last working day's date

Posted on 2014-10-15
Medium Priority
287 Views
Hi

I have a table called Performance, which has a date column called PDate.
I want to select all columns (Select * From Performance)
for the last working day
So if today is a Monday it will look at the last Friday and for all
other day's it will look one day back
How will the the SQL Statement look
0
Question by:Murray Brown
• 2

LVL 70

Assisted Solution

Scott Pletcher earned 1332 total points
ID: 40382539
SELECT *
FROM dbo.Performance
WHERE
PDate >= DATEADD(DAY, CASE DATEDIFF(DAY, 0, GETDATE()) % 7 WHEN 0 THEN -3 ELSE -1 END, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) AND
PDate < DATEADD(DAY, 1, DATEADD(DAY, CASE DATEDIFF(DAY, 0, GETDATE()) % 7 WHEN 0 THEN -3 ELSE -1 END, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)))

Btw, if that is typically how you query this table, by PDate, you should cluster the table on PDate.
0

LVL 32

Accepted Solution

awking00 earned 668 total points
ID: 40382552
select * from performance where PDate =
case when datename(dw,getdate()) = 'Monday' then dateadd(day,-3,getdate())
when datename(dw,getdate()) = 'Sunday' then dateadd(day,-2,getdate())
end;
0

LVL 70

Assisted Solution

Scott Pletcher earned 1332 total points
ID: 40382585
I'd probably add a CTE (or CROSS APPLY) to do the date calculation, both to make the code more self-documenting and to keep the final query to only its core elements.

I don't use setting-dependent values, such as 'Monday', if I can avoid it.  The technique I use is valid for any SQL settings, of COLLATION/case sensitivity, LANGUAGE, DATEFIRST, etc..

For date/datetime data types, I use the >= and < approach, as it's accurate even if the underlying data type changes, such as from date to datetime, or datetime to datetime2, etc..

CASE DATEDIFF(DAY, 0, GETDATE()) % 7
WHEN 6 THEN -2 --Sunday
WHEN 0 THEN -3 --Monday
ELSE -1 END,
)
SELECT *
FROM dbo.Performance
WHERE
0

Author Closing Comment

ID: 40383807
Thanks very much
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
###### Suggested Courses
Course of the Month10 days, 16 hours left to enroll