Select All Records Where Date Falls Within Next Week

• netguykb

  Ten Centuries

  Points: 1310

  More actions

  April 22, 2018 at 4:51 pm

  #334401

  Id like to run query that would display records where the Build Day occurs next week. So if I run a query from Monday April 16 up to Sunday April 22 then all records but one would show up in the query.

  CREATE TABLE dbo.TESTBUILDS (
[Build ID] int NOT NULL IDENTITY(1,1),
[Build Type] nvarchar(255) NULL,
[Build Name] nvarchar(255) NULL,
[Build Status] nvarchar(255) NULL,
[Build Day] datetime NULL
);

  INSERT INTO dbo.TESTBUILDS ([Build Type],[Build Name],[Build Status],[Build Day]) VALUES ('Private','RV9','ACTIVE','4/23/2018')
INSERT INTO dbo.TESTBUILDS ([Build Type],[Build Name],[Build Status],[Build Day]) VALUES ('Private','Hummel','ACTIVE','4/25/2018')
INSERT INTO dbo.TESTBUILDS ([Build Type],[Build Name],[Build Status],[Build Day]) VALUES ('Private','Volmer','ACTIVE','4/25/2018')
INSERT INTO dbo.TESTBUILDS ([Build Type],[Build Name],[Build Status],[Build Day]) VALUES ('Private','ULF1','ACTIVE','5/5/2018')
INSERT INTO dbo.TESTBUILDS ([Build Type],[Build Name],[Build Status],[Build Day]) VALUES ('Private','Cessna140','ACTIVE','4/27/2018')
INSERT INTO dbo.TESTBUILDS ([Build Type],[Build Name],[Build Status],[Build Day])VALUES ('Private','Glassair','ACTIVE','4/23/2018')

• Andrew P

  SSCarpal Tunnel

  Points: 4649

  More actions

  April 22, 2018 at 7:16 pm

  #1987814

  Here's how I might do this. I suspect the first day of the week could be identified simpler than I have here.
  DECLARE @RUNDATE DATETIME = GETDATE()

  -- Set the @@DATEFIRST setting to 1, and capture it so we can restore it back afterwards.
DECLARE @PREVDATEFIRST INT = @@DATEFIRST;
SET DATEFIRST 1 ;

  SELECT [BUILD DAY]
  -- Find out what the current weekday is, remove that many days (minus 1, as the first day of the week is day 1) from the date, then add 7 to get when next week starts. Add 14 to find when the week after starts.
  , DATEADD(DAY, 7, CAST(DATEADD(DAY, - (DATEPART(WEEKDAY, @RUNDATE) - 1), @RUNDATE) AS DATE)) AS STARTOFNEXTWEEK
  , DATEADD(DAY, 14, CAST(DATEADD(DAY, - (DATEPART(WEEKDAY, @RUNDATE) - 1), @RUNDATE) AS DATE)) AS ENDOFNEXTWEEK
FROM TESTBUILDS
WHERE TESTBUILDS.[BUILD DAY] >= DATEADD(DAY, 7, CAST(DATEADD(DAY, - (DATEPART(WEEKDAY, @RUNDATE) - 1), @RUNDATE) AS DATE))
  AND TESTBUILDS.[BUILD DAY] < DATEADD(DAY, 14, CAST(DATEADD(DAY, - (DATEPART(WEEKDAY, @RUNDATE) - 1), @RUNDATE) AS DATE))

  SET DATEFIRST @PREVDATEFIRST;

• Jeff Moden

  SSC Guru

  Points: 1003857

  More actions

  April 22, 2018 at 9:34 pm

  #1987835

  Between knowing that day "0" is the first of January, 1900 and that day was a Monday along with a little integer math and a little direct date math, it can be greatly simplified.

  DECLARE @Today DATETIME = '20180417'
;
 SELECT *
   FROM dbo.TESTBUILDS
  WHERE [Build Day] >= DATEADD(dd,DATEDIFF(dd,0,@Today)/7*7,7)
    AND [Build Day] <  DATEADD(dd,DATEDIFF(dd,0,@Today)/7*7,14)
;

  Just substitute GETDATE() for @Today in the formulas and forget the DECLARE, which was included just to demonstrate.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)