How to search and group by year with MSSQL
- There are many ways of searching and doing date calculations.
- Often when people are searching for a whole year for summary views or reports they will be using: between 1-Jan and 31-Dec...
- Microsoft SQL Server up to including SQL Server 2005, does not support dates data types, only date and time. This means that the date also carries the time, and therefore the search for between 1-Jan and 31-Dec will miss the last day for anything after midnight.
- Microsoft SQL Server 2008 does support date only data type. But it will be long time, before people will use them because of the carry over from previous versions. Only new applications/databases will have them.
- It is simpler to just select for the year 2007 than to have to explain to the end user that selecting between 1-Jan-2007 and 1-Jan-2008 will only include the year 2007.
Applies to:
- Microsoft SQL Server 2000
- Microsoft SQL Server 2005
- Microsoft SQL Server 2008
Data used
- USE sql911;
- go
- SELECT sales_date,sales_qty,sales_price,emp_short
- FROM sales
- ORDER BY sales_date;
- go
sales_date sales_qty sales_price emp_short ----------------------- --------------------------------------- --------------------------------------- ---------- 2004-03-06 14:01:01.000 0.00 NULL 000110 2004-10-06 10:01:01.000 0.00 NULL 000310 2005-03-25 14:01:01.000 5.00 11.75 000190 2005-04-06 15:01:01.000 4.00 11.75 000010 2005-05-06 16:01:01.000 3.00 11.75 000190 2005-10-06 10:01:01.000 10.00 11.75 000030 2005-11-21 10:01:01.000 9.00 11.75 000010 2005-11-30 10:01:01.000 0.00 11.75 000110 2005-12-06 11:01:01.000 8.00 11.75 000010 2005-12-06 11:01:01.000 8.00 11.75 000190 2005-12-22 10:01:01.000 10.00 11.75 000310 2006-01-06 12:01:01.000 7.00 11.75 000030 2006-01-22 12:01:01.000 7.00 11.75 000310 2006-02-23 13:01:01.000 6.00 11.75 000110 2006-02-28 13:01:01.000 0.00 NULL 000190 2006-03-01 16:01:01.000 3.00 11.75 000010 2006-03-06 14:01:01.000 5.00 11.75 000310 2006-03-19 14:01:01.000 5.00 11.75 000110 2006-03-31 17:01:01.000 0.00 11.75 000190 2006-04-06 15:01:01.000 4.00 11.75 000010 2006-04-20 15:01:01.000 4.00 11.75 000030 2006-06-06 17:01:01.000 0.00 NULL 000110 2006-06-06 17:01:01.000 2.00 11.75 000030 2006-07-06 00:00:00.000 1.00 11.75 000190 2006-07-06 19:01:01.000 1.00 11.75 000010 2006-07-06 19:01:01.000 1.00 11.75 000310 2006-07-06 19:01:01.000 1.00 11.75 000190 2006-10-06 10:01:01.000 10.00 11.75 000190 2006-10-06 10:01:01.000 10.00 11.75 000190 2006-10-06 10:01:01.000 10.00 11.75 000310 2006-10-06 10:01:01.000 10.00 11.75 000310 2006-10-06 10:01:01.000 10.00 11.75 000030 2006-10-29 10:01:01.000 3.00 11.75 000110 2006-10-31 10:01:01.000 10.00 11.75 000110 2007-04-18 00:00:00.000 0.00 11.75 000010 2007-05-03 09:00:00.000 3.00 11.75 000010 2007-05-05 04:00:00.000 1.00 11.75 000010 2007-05-07 00:00:00.000 2.00 10.00 000310 2007-05-07 11:00:00.000 2.00 11.75 000010 2007-05-07 11:30:00.000 2.00 10.50 000030 2007-05-08 05:00:00.000 1.00 10.00 000310 2007-05-08 05:00:00.000 0.00 10.00 000010 2007-05-08 07:00:00.000 2.00 10.00 000010 2007-05-08 11:00:00.000 3.00 10.00 000010 2007-05-08 13:30:00.000 1.00 10.00 000030 2007-05-08 15:00:00.000 1.50 11.75 000010 2007-05-09 14:00:00.000 1.25 10.00 000110 2007-05-10 14:00:00.000 5.00 10.00 000190 2007-05-11 13:00:00.000 3.50 10.00 000310 2007-05-11 14:15:00.000 0.00 10.00 000190 2007-05-12 15:00:00.000 0.00 10.00 000190 2007-05-13 16:00:00.000 1.00 10.00 000190 2007-05-14 11:00:00.000 3.00 10.00 000310 2007-05-31 00:00:00.000 5.00 11.75 000030 2007-06-17 17:01:01.000 2.00 11.75 000010 2007-07-06 00:00:00.000 1.00 11.75 000190 2007-11-06 10:01:01.000 9.00 11.75 000030 (57 row(s) affected)
Selecting for the year 2005 with datepart
- SELECT sales_date,sales_qty,sales_price,emp_short
- FROM sales
- WHERE DATEPART(yyyy,sales_date) = 2005
- ORDER BY sales_date;
- go
sales_date sales_qty sales_price emp_short ----------------------- --------------------------------------- --------------------------------------- ---------- 2005-03-25 14:01:01.000 5.00 11.75 000190 2005-04-06 15:01:01.000 4.00 11.75 000010 2005-05-06 16:01:01.000 3.00 11.75 000190 2005-10-06 10:01:01.000 10.00 11.75 000030 2005-11-21 10:01:01.000 9.00 11.75 000010 2005-11-30 10:01:01.000 0.00 11.75 000110 2005-12-06 11:01:01.000 8.00 11.75 000010 2005-12-06 11:01:01.000 8.00 11.75 000190 2005-12-22 10:01:01.000 10.00 11.75 000310 (9 row(s) affected)
Summarizing by year with datepart
- SELECT DATEPART(yyyy,sales_date) AS 'Year', SUM(COALESCE(sales_qty,0)) AS 'Units sold'
- FROM sales
- GROUP BY DATEPART(yyyy,sales_date);
Year Units sold ----------- --------------------------------------- 2004 0.00 2005 57.00 2006 110.00 2007 49.25 (4 row(s) affected)
- It's easier to use datepart for grouping than to use a large case statement, store the data to a temporary table and then report from the temporary table.
- If you have defined an index on the date column, the between will use it.
- The datepart calculation will not use the index, even if it exist. The datepart will do a full table scan. For 5000 rows: no problem, for 50 million rows that's another story.