CODEWITHSUNDEEP
sqlsql-serverselectgetdate
trx
trx

Reputation: 2167

Using getdate()

I am using the below query

SELECT *
    FROM [dbo].[QUANTITY] 
    WHERE getdate() BETWEEN fromdate AND todate

If the todate is null (1900-01-01 00:00:00.000) it just filters them out. I want even the fields with todate to be null (1900-01-01 00:00:00.000) in the result. How can this be done?

Upvotes: 0

Views: 78

Answers (3)

Anon
Anon

Reputation: 10918

SELECT *
FROM [dbo].[QUANTITY] 
WHERE getdate() >= fromdate
  AND getdate() <= COALESCE(todate
                           ,NULLIF(todate,'1900-01-01 00:00:00.000')
                           ,'9999-12-31 23:59:59.997'
                           )

Upvotes: 0

Nathan Teague
Nathan Teague

Reputation: 875

It seems like somewhere along the line there is a .Net or some program saving the Date.MinValue, if the application cannot be replaced you could always tack on a

datefromparts(1900,1,1)

and change the where clause to:

where getdate() > fromdate
and (todate = datefromparts(1900,1,1) or getdate() <= todate)

that would take either the max or unbounded for the todate. if it is possible, to prevent odd results, i would also use 

declare @testDate datetime = getdate();

and use that variable in the preceding where clause instead of getdate().

Upvotes: 0

Mureinik
Mureinik

Reputation: 312219

You could explicitly check it with a combination of and and or operators:

SELECT *
FROM   [dbo].[QUANTITY] 
WHERE  (getdate() >= fromdate AND
        (todate IS NULL OR
         todate = '1900-01-01 00:00:00.000')
       ) OR
       getdate() BETWEEN fromdate AND todate

Upvotes: 2

Related Questions