SQL Server - Filter by Dates in SQL using SQL Query.

Description:

In this example we explain that how to get the Last Month, Next Month, This Month, Last Week, Next week, This Week, Today, Yesterday, tomorrow created record in SQL Server. Or how to fetch or select the record based on Date filter in SQL Server. Or SQL Query to get or select the record based on different Date filter condition in SQL.

Here we demonstrate the basic idea or some important SQL Query of the Date that we have used in our daily routines.so below is the different SQL Query to get the record from SQL Server based on some different Date filter criteria.

SQL Query:

--Get Last Month Record

SELECT *

FROM [dbo].[Orders]

WHERE DATEPART(m, [OrderDate]) = DATEPART(m, DATEADD(m, -1, getdate()))

AND DATEPART(yyyy, [OrderDate]) = DATEPART(yyyy, DATEADD(m, -1, getdate()))

-- Get This month Record

SELECT *

FROM [dbo].[Orders]

WHERE

MONTH([OrderDate]) = MONTH(dateadd(dd, -1, GetDate()))

AND

YEAR([OrderDate]) = YEAR(dateadd(dd, -1, GetDate()))

--Get Next Month Record

SELECT *

FROM [dbo].[Orders]

WHERE DATEPART(m, [OrderDate]) = DATEPART(m, DATEADD(m, +1, getdate()))

AND DATEPART(yyyy, [OrderDate]) = DATEPART(yyyy, DATEADD(m, +1, getdate()))

--Get Last Week Record

select * from [dbo].[Orders] where [OrderDate] >=  dateadd(wk, datediff(wk, 0, getdate()) - 1, 0)  --first day of last week

and [OrderDate] < dateadd(wk, datediff(wk, 0, getdate()), 0) --first day of this week

--Get This week Record

SELECT * from [dbo].[Orders] where [OrderDate] >= dateadd(day, 1-datepart(dw, getdate()), CONVERT(date,getdate()))

AND [OrderDate] <  dateadd(day, 8-datepart(dw, getdate()), CONVERT(date,getdate()))

--Get Next week Record

SELECT * from [dbo].[Orders] where [OrderDate] >=   dateadd(wk, datediff(wk, 0, getdate()) + 1, 0)

AND [OrderDate] <  dateadd(wk, datediff(wk, 0, getdate())+1, 7)

--Get Today Record

SELECT * from [dbo].[Orders] where [OrderDate] =   dateadd(day,datediff(day,0,GETDATE()),0)

--Get Yesterday Record

SELECT * from [dbo].[Orders] where [OrderDate] =   dateadd(day,datediff(day,1,GETDATE()),0)

--Get Tomorrow Record

SELECT * from [dbo].[Orders] where [OrderDate] =  dateadd(day,datediff(day,-1,GETDATE()),0)

--Get This Year Record

SELECT * from [dbo].[Orders] where  YEAR([OrderDate]) = YEAR(GEtdate());

--Get Next Year Record

SELECT * from [dbo].[Orders] where  YEAR([OrderDate]) = YEAR(GEtdate())+1

--Get Last Year Record

SELECT * from [dbo].[Orders] where  YEAR([OrderDate]) = YEAR(GEtdate())-1