August 26, 2015 at 12:27 pm
In my join clause I am getting date between 20 to 25 days old. I only want 21 days old date, I replaced the below code with the following
but I get no output. please someone advise
AND AppDetails.DateDetailDisposed = Getdate() - 21 INNER JOIN
MP100.dbo.Products Products ON AppDetails.ProductID = Products.ProductID
WHERE tblDocumentRequests.DocumentID IN ( 5, 231 )
AND AppDetails.DateDetailDisposed BETWEEN getdate() - 25 AND getdate() - 20 INNER JOIN
MP100.dbo.Products Products ON AppDetails.ProductID = Products.ProductID
Select Statement if needed
CONVERT(char(10),AppDetails.DateDetailDisposed, 101) AS DecisionDate
August 26, 2015 at 12:44 pm
AND AppDetails.DateDetailDisposed >= Dateadd(Day, Datediff(Day, 0, Getdate()), 0) - 21
AND AppDetails.DateDetailDisposed < Dateadd(Day, Datediff(Day, 0, Getdate()), 0) - 20 INNER JOIN
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
August 26, 2015 at 12:49 pm
Without knowing the data types of your table, here's an example set to help you understand what's going on and how to fix your problem.
CREATE TABLE #TestDate(
myDatetime datetime,
myDate date
)
INSERT INTO #TestDate
VALUES( GETDATE()-21, GETDATE()-21)
WAITFOR DELAY '00:00:00.003'
SELECT GETDATE()-21 --This is the new value, which is different from the one in the table
SELECT * FROM #TestDate WHERE myDatetime = GETDATE()-21 --No results because the time is different
SELECT * FROM #TestDate WHERE myDate = GETDATE()-21 --The value in myDate has no time, so it's different.
SELECT * FROM #TestDate WHERE myDatetime = CAST( GETDATE()-21 AS date) --The value CAST( GETDATE()-21 AS date) has no time and is different from the value from the column
SELECT * FROM #TestDate WHERE myDate = CAST( GETDATE()-21 AS date) --Only uses dates so it match
SELECT * FROM #TestDate
WHERE myDatetime >= DATEADD( dd, DATEDIFF(dd, 0, GETDATE())-21, 0)
AND myDatetime < DATEADD( dd, DATEDIFF(dd, 0, GETDATE())-20, 0) -- Includes the range that includes the whole day.
SELECT DATEADD( dd, DATEDIFF(dd, 0, GETDATE())-21, 0) DateMinus21,
DATEADD( dd, DATEDIFF(dd, 0, GETDATE())-20, 0) DateMinus20
GO
DROP TABLE #TestDate
August 26, 2015 at 1:01 pm
Thanks Scott and SSC
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply