Introduction
In this article i am going to explain how you can pass date range condition where clause in SQL server and also show you how you can filter record from particular table based on condition in where clause in SQL server as well as also explains how you can convert format of date in dd/MM/yyyy or yyyy-MM-dd in SQL server.
In my previous articles i explained How to Remove Duplicate Records From Table Using UNION And EXCEPT Operator and How to Find Created and Modified Stored Procedure With Date and Time and How to Create and Execute Parameterized Stored Procedure From Another Stored Procedure and How to Export JSON Data to Excel/CSV File using AngularJs With Bootstrap as well as also explained How to Create AngularJs Table with Bootstrap 4 in ASP.NET Web Forms and in this article I will show you how to use date criteria in where clause in SQL server.
While you working with any data-driven applications sometimes you need to fetch and filter data, update or delete data from the table based on your requirement. sometimes we have date range and based on that date range we need to update, delete or select data from any tables available in your database either that is from a single table or multiple tables using joins.
What is WHERE Clause in SQL Server?
In SQL Server WHERE clause is used to filter the records from the table. It is also used to extract only those records that fulfill a specified condition passed in WHERE clause. It is not only used in the SELECT statement, but it is also used in UPDATE and DELETE statement.
Requirement
1) Create/Declare a temporary table with name "Employee".
2) Insert some dummy records on "Employee" table for demonstration.
3) Selects all the employees from employee table whose date of birth between 01-01-1996 to 01-01-1997.
4) Date Criteria should be optional.
4) If User didn't enter/select any date then all record should appear as a result set.
Implementation
So, let's start with an example and first we will declare a table with name Employee based on given requirement.
DECLARE @Employee AS TABLE ( EmpId INT, FirstName VARCHAR(50), LastName VARCHAR(50) , Department VARCHAR(50), Designation VARCHAR(50), Country VARCHAR(50), DateOfBirth DATETIME )Now, we will insert some dummy records on the Employee table, for demonstration.
INSERT INTO @Employee VALUES (1, 'Nikunj', 'Satasiya', 'IT', 'Software Engineer', 'India', '1996-04-08 00:00:00:00 '), (2, 'Hiren', 'Dobariya', 'IT', 'Web Devloper', 'India', '1996-12-31 00:00:00:00'), (3, 'Krishna', 'Patel', 'IT', 'Web Devloper', 'UK', '1995-10-05 00:00:00:00'), (4, 'Vivek', 'Ghadiya', 'IT', 'Software Engineer', 'India', '1993-12-18 00:00:00:00'), (5, 'Pratik', 'Pansuriya','IT', 'Software Engineer', 'India', '1997-01-16 00:00:00:00'), (6, 'Sneha', 'Patel', 'IT', 'Web Devloper', 'India', '1996-08-07 00:00:00:00')If you read the given requirement then we need to fetch all the records of the employee whose birthday coming in the given date range passed as criteria in where clause and date criteria should be optional, it means if the user didn't select any date range then all the appropriate records should be displayed as a result set.
So, First, we will declare two parameters for From and To date Criteria.
DECLARE @From_Birth_Date VARCHAR(10)='1996-01-01', @To_Birth_Date VARCHAR(10)='1997-01-01'Now, we will write a select statement to archive our requirement.
--Paramiter Declaration-------------------- DECLARE @From_Birth_Date VARCHAR(10)='1996-01-01', @To_Birth_Date VARCHAR(10)='1997-01-01' SELECT * FROM @Employee Emp WHERE -------------1st Date Criteria For FromDate----------------- ( CAST(Emp.DateOfBirth AS DATE) >= CAST(@From_Birth_Date AS DATE) OR 1 = ( CASE WHEN LTRIM(RTRIM(@From_Birth_Date)) = '' THEN 1 ELSE 0 END ) ) -------------2nd Date Criteria For ToDate----------------- AND ( CAST(Emp.DateOfBirth AS DATE) <= CAST(@To_Birth_Date AS DATE) OR 1 = ( CASE WHEN LTRIM(RTRIM(@To_Birth_Date)) = '' THEN 1 ELSE 0 END ) )If you analyzed above select statement then we have passed the date range in where clause as date criteria. we have cast/convert our column DateOfBirth in "yyyy-MM-dd" formate.
How to Convert/Cast Date in "dd/MM/yyyy" format?
SELECT CONVERT(VARCHAR(10),GETDATE(),103) As [Date]
How to Convert/Cast Date in "yyyy-MM-dd" format?
SELECT CAST(GETDATE() AS DATE) As [Date]We have used date condition something like shown below, where CAST(Emp.DateOfBirth AS DATE) >= CAST(@From_Birth_Date AS DATE) return all the records from employee table whose date of birth is equal to or greater than equal to From_Birth_Date. Now suppose user didn't pass the From_Birth_Date as a date criteria then we also used another condition with "OR" operator with this condition where we checked if From_Birth_Date is blank then our condition should be 1=1 else 1=0.
( CAST(Emp.DateOfBirth AS DATE) >= CAST(@From_Birth_Date AS DATE) OR 1 = ( CASE WHEN LTRIM(RTRIM(@From_Birth_Date)) = '' THEN 1 ELSE 0 END ) )Now, we have passed our second date criteria for To_Birth_Date same as above just our conditions will change, here we will use something like CAST(Emp.DateOfBirth AS DATE) <= CAST(@To_Birth_Date AS DATE) and this condition will return all the records from employee table whose date of birth is less than or equal to To_Birth_Date. and also used "OR" operator with this condition where we checked if To_Birth_Date is blank then our condition should be 1=1 else 1=0.
AND ( CAST(Emp.DateOfBirth AS DATE) <= CAST(@To_Birth_Date AS DATE) OR 1 = ( CASE WHEN LTRIM(RTRIM(@To_Birth_Date)) = '' THEN 1 ELSE 0 END ) )
Output
With Date Criteria
 |
| SQL Server Date Range Condition in WHERE Clause with Date Criteria |
Without Date Criteria
 |
| SQL Server Date Range Condition in WHERE Clause Without Date Criteria |
Summary
This article explains how to use date range criteria in where clause and how to write/use where clause in SQL server.
