Last post Feb 03, 2019 12:35 PM by mgebhard
Feb 03, 2019 07:52 AM|robby32|LINK
I have to filter rows which have a mopnth and year greater than a certain value.
I have the following , but it seems to filter everything.
declare @Today Datetime
declare @ExtractionMonth INT
declare @ExtractionYear INT
Set @Today=getdate() --date will equal today
Set @ExtractionMonth = MONTH(@Today)
Set @ExtractionYear = YEAR(@Today)
--SELECT @ExtractionMonth, @ExtractionYear (if ran today it would give @ExtractionMonth = 3 and @ExtractionYear = 2019
Select Enddate, *
WHERE ( @ExtractionMonth >= MONTH(Enddate)) and (@ExtractionYear >= YEAR(Enddate))
for example i have rows with a enddate of
2011-01-28, 2014-01-31 , 2018-02-16, 2018-02-23, 2018-02-25, 2019-02-15
I have listed them horizontally , but they are in the enddate column for each row.
So out of these only the row with enddate 2019-02-15 should be filtered , but i seem to get all these and the ones with no date .
any idea on what the issue may be ?
Feb 03, 2019 12:35 PM|mgebhard|LINK
IF OBJECT_ID('tempdb..#Dates') IS NOT NULL
DROP TABLE #Dates
CREATE TABLE #Dates (EndDate DATETIME)
INSERT INTO #Dates(EndDate)
DECLARE @EndDate DATETIME
DECLARE @Today DATETIME = GETDATE()
SET @EndDate = DATEFROMPARTS(YEAR(@Today), MONTH(@Today), 1)
WHERE EndDate >= @EndDate
SQL reference documents cover DateTime functions.