Get SQL Server time functions related to year, quarter, month, and end of week

--Current Date: 2013-12-31

Select CONVERT (varchar), GETDATE (), 120)

--Current date + time: 2013-12-31 14:36:46.860

Select GETDATE ()

--the first day of one months: 2013-12-31 00:00:00.000

Select DATEADD (Mm,datediff (Mm,0,getdate ()), 0)

--This week's Monday: 2013-12-30 00:00:00.000

Select DATEADD (Wk,datediff (Wk,0,getdate ()), 0)

--the first day of the year: 2013-01-01 00:00:00.000

Select DATEADD (Yy,datediff (Yy,0,getdate ()), 0)

--First day of the quarter: 2013-10-01 00:00:00.000

Select DATEADD (Qq,datediff (Qq,0,getdate ()), 0)

-Last day of last month: 2013-11-31 23:59:59.997

Select DATEADD (Ms,-3,dateadd (mm, DateDiff (Mm,0,getdate ()), 0))

-Last day of last year: 2012-12-31 23:59:59.997

Select DateAdd (Ms,-3,dateadd (yy, DateDiff (Yy,0,getdate ()), 0))

--the last day of the month: 2013-12-31 23:59:59.997

Select DATEADD (Ms,-3,dateadd (mm, DateDiff (M,0,getdate ()) +1, 0))

--the last day of the year: 2013-12-31 23:59:59.997

Select DateAdd (Ms,-3,dateadd (yy, DateDiff (Yy,0,getdate ()) +1, 0)

--The first Monday of this month: 2013-12-02 00:00:00.000

Select DateAdd (wk, DateDiff (wk,0, DateAdd (Dd,6-datepart ()), Day,getdate ()), 0)

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/SQLServer/