Comparing Dates in Sql Server. Using Date Columns in where clause in SQL Select Statements. Using Greater than or lesser than or between operator in SQL Server

There are sql statements where we need to filter records based on columns of type Datetime, filtration of records based on dates is one of the tricky part in sql server, this post tries to simplify things.

There are two methods, first method uses the Greater than and lesser than operator, second method uses the between operator

Method 1) Using Lesser than (<) and Greater than (>) operator 

Example: To Get the Records between 26 January 2015 and 30 January 2015, query is as follows
Select * From Tbl_Employee_master where CreatedDate > '2015-01-26' and CreatedDate < '2015-01-30'

Example: To Get the Records Created after 26 January 2015
Select * From Tbl_Employee_master where CreatedDate > '2015-01-26'

We can include time also in the query as follows -
Select * From Tbl_Employee_master where CreatedDate > '2015-01-26 23:59:59.999'

Method 2) using BETWEEN operator

Example: To Get the Records between 26 January 2015 and 30 January 2015, query is as follows
Select * From Tbl_Employee_master where CreatedDate between '2015-01-26' and  '2015-01-30'

We can include time also in the query as follows -
Select * From Tbl_Employee_master where CreatedDate BETWEEN '2015-01-26' and  '2015-01-30 23:59:59.999'