SQL Query to Get Month wise Data, Year wise Data

Introduction

In this SQLServer 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 SQL Server - Get List of All Stored Procedures from Database in SQL Server , and many articles relating to SQLServer.  Now I will explain how to write query to get month wise data or year wise data from database in SQLServer.

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

In this way we can get month wise or year wise records count from database using  SQLServer.