SQL Query to Get Month wise Data, Year wise Data

SQL Query to Get Month wise Data, Year wise Data

By: Suresh Dasari Nov 29, 2012

Introduction

In this SQL Server article I will explain how to write query to get monthly (month wise) data or year wise data from database.

Description

In previous articles I explained Query to get Exclude weekends in SQL Query, While Loop Example in SQL Server, Find First and last day of current month in SQL, Convert Rows to Columns in SQL, Joins in SQL Server and many articles relating to SQL Server.  Now I will explain how to write query to get month wise data or year wise data from database in SQL Server.

If we want to get data based on month wise or year wise we need to write the query like as shown below 

SELECT YEAR(DateColumn) AS [Year],MONTH(DateColumn) AS [Month],COUNT(*) from yourtable Group By YEAR(DateColumn) MONTH(DateColumn)

If you want to see it in complete example first design one table (UserInfo) with two columns in database and enter some dummy data like as show below 

Type	CreateDate
Technology	2012-11-18 09:00:00:00
Social Network	2012-11-28 09:29:00:00
Education	2012-11-30 10:30:20:00
Politics	2012-10-04 04:20:33:59
Software	2012-10-03 04:20:33:59
Hardware	2012-10-02 04:20:33:59
Teaching	2012-10-01 04:20:33:59
Tech Support	2012-09-28 09:00:00:00
Customer Support	2012-09-10 09:00:00:00

Now from above table we need to get the records month wise and year wise for that we need to write the query like as shown 

DECLARE @temp TABLE (ID INT,Location varchar(120)) INSERT INTO @temp VALUES(1,'Near to Men''s Hostel') SELECT * FROM @temp

Once we run above query we will get output like as shown below

Output:

Year	Month	Count
2012	9	2
2012	10	4
2012	11	3