SQL search for date between values or Min/Max if NULL

Question

I have a T-SQL stored procedure where I want to search for a particular value and optionally limit the search to particular dates if they are passed in. If null values are passed in for either of these dates, then I want to ignore those. The way I am thinking of doing this by setting the input dates to minimum or maximum if they are null. I would prefer not to hardcode the minimum and maximum values though. So I am wondering what the SQL equivalent of the C# DateTime.MaxValue and DateTime.MinValue are.

I am thinking of using Coalesce like so

SELECT EmployeeName FROM Employee
WHERE  EmployeeID = @EmployeeId AND
Birthday BETWEEN Coalesce(@StartDate, <MinDateTime>) AND  
                 Coalesce(@EndDate, <MaxDateTime>)

Is there a built in function/constant/variable/enum I can use for the <MinDateTime> and <MaxDateTime> variables?

Any suggestions?

Solution 1

There's no such functionality in SQL Server. You can easily find the min and max dates allowed in BOL (1753-01-01 - 9999-12-31). Or you could hard code another date easily (if you really are working with birthdays, 1800-01-01 - 2100-12-31 would probably suffice). Or you could (if it is the range query you've shown), have the coalesce fall back to the birthday itself:

SELECT EmployeeName FROM Employee
WHERE  EmployeeID = @EmployeeId AND
Birthday BETWEEN Coalesce(@StartDate, Birthday) AND  
                 Coalesce(@EndDate, Birthday)

But note that this will not necessarily scale well for very large tables.

Edited after accept, to respond to comment from OP

Generally, for SQL, if you're needing "reference" data frequently, you add it as a table yourself. (Google for "calendar table" or "number table sql"). So in this instance, if you wanted to, you could add a "constants" (or maybe "limits" table):

create table Constants (
    Lock char(1) not null,
    datetimeMin datetime not null,
    datetimeMax datetime not null,
    intMin int not null,
    intMax int not null,
    /* Other Min/Max columns, as required */
    constraint PK_Constants PRIMARY KEY (Lock),
    constraint CK_Constants_Locked CHECK (Lock='X')
)
insert into Constants (Lock,datetimeMin,datetimeMax,intMin,intMax)
select 'X','17530101','99991231',-2147483648,2147483647

Which you could then reference in queries (either through a subselect, or by cross joining to this table). E.g.

SELECT EmployeeName
FROM Employee, Constants
WHERE  EmployeeID = @EmployeeId AND
Birthday BETWEEN Coalesce(@StartDate, Constants.datetimeMin) AND  
                 Coalesce(@EndDate, Constants.datetimeMax)

(The Lock, Primary Key, and Check constraint, work together to ensure that only a single row will ever exist in this table)

Solution 2

For SQL Server specifically, according to BOL, the limits are:

• datetime: 1753-01-01 00:00:00 through 9999-12-31 23:59:59.997
• smalldatetime: 1900-01-01 00:00:00 through 2079-06-06 23:59:29.998
• date: 0001-01-01 through 9999-12-31
• datetime2: 0001-01-01 00:00:00 through 9999-12-31 23:59:59.9999999

As you can see, it depends on your exact data type.

As for the query, I would do it like this:

SELECT EmployeeName
FROM Employee
WHERE  EmployeeID = @EmployeeId
 AND  (@StartDate IS NULL
  OR   Birthday >= @StartDate)
 AND  (@EndDate   IS NULL
  OR   Birthday <= @EndDate)

Solution 3

There are no built in functions to get the minimum or maximum date time values. You have to hard code the values or retrieve the values from the database.

If the parameters are NULL you could just select the min & max dates from the Employee table.

IF (@StartDate IS Null)
BEGIN
 SELECT @StartDate = MIN(Birthday) FROM Employee
END


IF (@EndDate IS Null)
BEGIN
 SELECT @EndDate = MAX(Birthday) FROM Employee
END

SELECT EmployeeName FROM Employee
WHERE  EmployeeID = @EmployeeId AND
Birthday BETWEEN @StartDate AND  @EndDate

Comments

[Michel de Ruiter]

I don't think this is what Chaitanya meant.

[Jagmag]

+1 It might not be exactly what Chaitanya was asking for but it meets the requirements well enough. No hardcoding of min and max datetime needed and same query can be used for null cases as well

[Unreason]

+1 for references. As for the query - it needs to be tested, BETWEEN ... COALESCE might be more understandable to the query planner.

[Chaitanya]

Very interesting. I had never seen the Coalesce(@StartDate, Birthday) kind of pattern before. I think creating a variable called maxdate and mindate and setting its values to the hardcoded dates might be a lot more readable. But yeah, the main info I was looking for was the lack of the enums/constants in SQL

[Sergii Volchkov]

Re COALESCE trick - +1 to the statement that it would not scale for large tables, SQL Server does not seem to be able to use index on Birthday column. Hard coding min and max dates works well with the index.