Introduction
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.