CODEWITHSUNDEEP
sql-serversql-server-2008t-sqlsql-server-2008-r2
Chris Cannon
Chris Cannon

Reputation: 1167

Equivalent of GETDATE() BETWEEN Two DateTime Fields where Both can be NULL

Is

SELECT [Id]
      ,[DateOnline] --Nullable
      ,[DateOffline] --Nullable
      ,[PageId]
      ,[DownloadId]
      ,[Weight]
  FROM [DownloadPage]
  WHERE GETDATE() BETWEEN [DateOnline] AND [DateOffline]

Equivalent to:

SELECT [Id]
      ,[DateOnline] --Nullable
      ,[DateOffline] --Nullable
      ,[PageId]
      ,[DownloadId]
      ,[Weight]
  FROM [DownloadPage]
  WHERE ([DateOnline] IS NULL OR [DateOnline] <= GETDATE())
  AND ([DateOffline] IS NULL OR [DateOffline] > GETDATE())

But with also catering for NULLs?

Or is there a more elegant way of doing it?

Where are parentheses needed here?

Thanks.

EDIT:

Both [DateOnline] AND [DateOffline] are of type DateTime

If [DateOnline] is NULL then the logic is "online now"

If [DateOffline] is NULL then the logic is "never go offline (once online)"

Sorry, I should have included this in my question to begin with.

Upvotes: 4

Views: 19799

Answers (2)

sam yi
sam yi

Reputation: 4934

The author's second query will net better performance even if there is no indexes on those columns. If there are indexes, that's a no brainer... using coalesce will disable the index and do a table scan instead of index seek (very bad for performance).

Even if there aren't any indexes on those columns, "is null" will return a constant... whereas, in the case of coalesce function, the NULL values will still need to be evaluated each time. If you have a table full of NULL DateOnline and DateOffline, this performance leak cannot be ignored.

In any case, I can't think of a reason why you'd use coalesce in this case.

Also, I'm guessing (since you're checking date range) that those two dates are all or nothing. You probably only have to check for one of those dates.

 WHERE ([DateOnline] IS NULL)
 OR GETDATE() BETWEEN [DateOnline] AND [DateOffline]

Upvotes: 5

mellamokb
mellamokb

Reputation: 56769

You can use COALESCE to convert nulls into a meaningful value. In this example I've chose default values that will always be inside the valid range.

WHERE GETDATE() BETWEEN COALESCE([DateOnline] , '1900-01-01')
                    AND COALESCE([DateOffline], '2099-12-31')

Demo: http://www.sqlfiddle.com/#!3/def09/1

Upvotes: 3

Related Questions