Tags: datepart function SQL used date month year grouping group by group 2013-12-26 15:31 20764 People read reviews (1) Favorite report Category: SQL Server (21) Copyright notice: This article for Bo Master original article, not allowed to reprint without Bo master. [SQL] View plain copy--take 2013-12-10 12:56:55 for example--convert (nvarchar (ten), createdate,120) = 2013-12-10--datepart (month , createdate) = 12--datepart (year,createdate) and 2013--can also be like this-year select DATEPART (years, ' 2013-06-08 ') select DATEPART (yyyy, ' 2013-06-08 ') Select DatePart (yy, ' 2013-06-08 ')--month select DATEPART (month, ' 2013-06-08 ') Select DATEPART ( MM, ' 2013-06-08 ') Select DATEPART (M, ' 2013-06-08 ')--day Select DATEPART (dd, ' 2013-06-08 ') the number of days in the--1 year select datepart (dy, ' 2013-06-08 ')--Quarterly Select DATEPART (QQ, ' 2013-06-08 ')--1 in the first week of the year Select DATEPART (wk, ' 2013-06-08 ')--week Select DATEPART (DW, ' 2013-06-08 ' will output 7select datepart (Weekday, ' 2013-06-08 ') will output 7select datename (Weekday, ' 2013-06-08 ') will output Saturday/* * Set Language to English */set language n ' 中文版 '--Set language for English select Datename (Weekday, ' 2013-06-08 ') will output saturday/* * Set Language to Simplified Chinese */set language N ' Simp Lified Chinese '--set language for Simplified Chinese Select datename (Weekday, ' 2013-06-08 ') outputsSaturday/* * Set the first day of the week for Monday */set Datefirst 1--Set the first day of the Week for Monday (the first day of the week in the United States is accustomed to Sunday) Select DATEPART (Weekday, ' 2013-06-08 ') will output 6/* * Set one week The first day for Sunday */set datefirst 7--Set the first day of the Week for Sunday (the first day of the week in the United States is accustomed to Sunday) Select DATEPART (Weekday, ' 2013-06-08 ') will output 7/* * Query the current session Datefir St Set Value */select @ @datefirstSELECT convert (varchar), GETDATE (),--2015-07-13select convert (varchar), GETDATE ( ), 101)--07/13/2015--grouped by day: 2013-01-01select convert (nvarchar), createdate,120) as Times,isnull (sum (Unit), 0.0) as Drinking from Pdt_outgroup by convert (nvarchar, createdate,120) go--Monthly Group: 2012-01select DATEPART (month,createdate) As Times,sum (Unit) as Totals from Pdt_outgroup by DATEPART (month,createdate) go--annually: 2013select DATEPART (year, CreateDate) as Times,sum (Unit) as Totals from Pdt_outgroup by DATEPART (year,createdate) go
SQL is grouped by date, month, year, group by group, DatePart function
