SQL Server

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 -
  1. Create Procedure USP_GetEmplyees  
  2. @FROMDATE DATE = Null,  
  3. @TODATE DATE = Null   
  4. AS  
  5. BEGIN  
  6.     SELECT EmpId, EMpName,Gender,CreatedOn,Status  
  7.     FROM Mas_Employee  
  8.         WHERE Status = 'A' AND  
  9.         Convert(varchar(10),CreatedOn,120)   
  10.         BETWEEN ISNULL(@FROMDATE,'1900-01-01') AND ISNULL(@TODATE,'9999-12-31')  
  11. END 
Test the Stored procedure by executing the following statements -
  1. --Return all the Employees  
  2. EXEC USP_GetEmplyees  
  3. EXEC USP_GetEmplyees Null  
  4. EXEC USP_GetEmplyees Null, Null  
  5.   
  6. --Return the Employees from begining to '2015-03-28'  
  7. EXEC USP_GetEmplyees Null, '2015-03-28'  
  8.   
  9. --Return the Employees from '2015-03-28' and above dates  
  10. EXEC USP_GetEmplyees '2015-03-28', Null  
  11.   
  12. --Return the Employees from '2015-03-28' to '2015-03-28'  
  13. EXEC USP_GetEmplyees '2015-03-28', '2015-03-28' 
I hope you enjoyed it. Please provide the best ways by commenting to this blog. 
Ebook Download
View all
Learn
View all
Membership not found