Date Function with ISNULL and between Operator in SQL Server

I have often read a common question in forum posts of how to use ISNULL and Between Operators together with Date Functions in stored procedure. So here is the solution that i provided in this blog.

Create the following table to demonstrate the ISNULL and Between Operators together with Date Functions in stored procedure.

Next Create Stored Procedure using ISNULL and Between Opertaors together with Date Functions -

Create Procedure USP_GetEmplyees
@FROMDATE DATE = Null,
@TODATE DATE = Null
AS
BEGIN
SELECT EmpId, EMpName,Gender,CreatedOn,Status
FROM Mas_Employee
WHERE Status = 'A' AND
Convert(varchar(10),CreatedOn,120)
BETWEEN ISNULL(@FROMDATE,'1900-01-01') AND ISNULL(@TODATE,'9999-12-31')
END

Test the Stored procedure by executing the following statements -

--Return all the Employees
EXEC USP_GetEmplyees
EXEC USP_GetEmplyees Null
EXEC USP_GetEmplyees Null, Null
--Return the Employees from begining to '2015-03-28'
EXEC USP_GetEmplyees Null, '2015-03-28'
--Return the Employees from '2015-03-28' and above dates
EXEC USP_GetEmplyees '2015-03-28', Null
--Return the Employees from '2015-03-28' to '2015-03-28'
EXEC USP_GetEmplyees '2015-03-28', '2015-03-28'

I hope you enjoyed it. Please provide the best ways by commenting to this blog.