Quick GETDATE question

  • 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

  • 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) A socialist is someone who will give you the shirt off *someone else's* back.

  • 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

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • 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