May 24, 2018 at 9:59 am
Hi Guys, I am using the below to show data relating to yesterdays date, It works great but on a Monday I need this to show for Friday, Saturday and Sunday rather than just yesterday. Is this possible?
Thanks
DECLARE @sd DATETIME;
DECLARE @ed DATETIME;
-- set the start date yesterday
SET @sd = dateadd(day,datediff(day,1,GETDATE()),0)
-- set end dates - today
SET @ed = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0);
May 24, 2018 at 10:23 am
craig.jenkins - Thursday, May 24, 2018 9:59 AMHi Guys, I am using the below to show data relating to yesterdays date, It works great but on a Monday I need this to show for Friday, Saturday and Sunday rather than just yesterday. Is this possible?Thanks
DECLARE @sd DATETIME;
DECLARE @ed DATETIME;
-- set the start date yesterday
SET @sd = dateadd(day,datediff(day,1,GETDATE()),0) 
-- set end dates - todaySET @ed = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0);
Try this:-- set the start date yesterday or Friday if yesterday is Sunday
DECLARE @sd AS datetime =
CASE DATEPART(weekday, DATEADD(day, -1, GETDATE()))
WHEN 1 THEN CONVERT(date, DATEADD(day, -3, GETDATE()))
ELSE CONVERT(date, DATEADD(day, -1, GETDATE()))
END;
-- set end dates - today
DECLARE @ed AS datetime = CONVERT(date, GETDATE());
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 24, 2018 at 10:45 am
Here you go. Use a CASE. Also, use DATETIME2 and SYSDATETIME(). Always assign a date variable, then use the current date as a default. This let's you test, and reuse the code for other purposes.
DECLARE @sd DATETIME2(3) ,
@ed DATETIME2(3) ,
@mydate DATETIME2(3) = SYSDATETIME();
SELECT @mydate = '2018-05-21';
-- set the start date yesterday
-- set end dates - today
SET @sd = CASE
WHEN DATENAME(dw, @mydate) = 'Monday' THEN
DATEADD( DAY, -3, DATEADD(DAY, DATEDIFF(DAY, 0, @mydate), 0))
ELSE
DATEADD(DAY, DATEDIFF(DAY, 1, @mydate), 0)
END;
SET @ed = DATEADD(DAY, DATEDIFF(DAY, 0, @mydate), 0);
SELECT @sd ,
@ed;
SELECT @sd , @ed;
May 28, 2018 at 7:54 am
Hello try this,
WITH DATERANGE AS
(
SELECT DT = DATEADD(DD, 0, T.STARTDT), ET=T.ENDDATE
FROM (SELECT
CASE WHEN DATENAME(DW, DATEADD(DAY, 0, GETDATE())) != 'MONDAY'
THEN DATEADD(DAY, -1, GETDATE())
ELSE DATEADD(DAY, -3, GETDATE()) END STARTDT,
CASE WHEN DATENAME(DW, DATEADD(DAY, 0, GETDATE())) != 'MONDAY'
THEN DATEADD(DAY, -1, GETDATE())
ELSE DATEADD(DAY, -1, GETDATE()) END ENDDATE
) T
WHERE DATEADD(DD, 0, T.STARTDT) <= T.ENDDATE
UNION ALL
SELECT DATEADD(DD, 1, DT), ET
FROM DATERANGE
WHERE DATEADD(DD, 1, DT) <= ET
)
SELECT DT
FROM DATERANGE
May 29, 2018 at 4:49 am
craig.jenkins - Thursday, May 24, 2018 9:59 AMHi Guys, I am using the below to show data relating to yesterdays date, It works great but on a Monday I need this to show for Friday, Saturday and Sunday rather than just yesterday. Is this possible?Thanks
DECLARE @sd DATETIME;
DECLARE @ed DATETIME;
-- set the start date yesterday
SET @sd = dateadd(day,datediff(day,1,GETDATE()),0) 
-- set end dates - todaySET @ed = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0);
-- Set Monday as the First Day
Set DateFirst 1;
Declare
@sd As Date = Case Datepart(weekday, GetDate()) when 1 then DateAdd(day,-3,GetDate()) else GetDate() end,
@ed As Date = GetDate()
Select @sd As StartDate, @ed As EndDate;
May 31, 2018 at 3:45 pm
I'd be inclined to use a calendar table that includes columns to indicate the days that are likely to be excluded (whether that be weekends or a more general non-working days thing) because this type of problem almost always ends up in a change of requirements along the lines of "Oh, well when it's the Tuesday after a Bank Holiday Monday then you need to show everything since Friday" or similar.
With a suitable table it's a relatively straightforward LAG function select to get the previous suitable day to start on.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply