SQL statement at the beginning of the month eliminate critical bug-free Date Daquan date difference

From: http://blog.csdn.net/timefile/article/details/6573821

Select dateadd (DD,-day (dateadd (month,-1, getdate () + 1, dateadd (month,-1, getdate ()/* 1st day of last month */
Select dateadd (DD,-day (getdate (), getdate ()/* end of last month */
Select dateadd (DD,-day (getdate () + 1, getdate ()/* 1st day of this month */
Select dateadd (DD,-day (dateadd (month, 1, getdate (), dateadd (month, 1, getdate ()/* end of this month */
Select dateadd (DD,-day (dateadd (month, 1, getdate () + 1, dateadd (month, 1, getdate ()/* 1st day of next month */
Select dateadd (DD,-day (dateadd (month, 2, getdate (), dateadd (month, 2, getdate ()/* end of next month */

If no time is displayed, convert (nvarchar (10), time, 120)

--------------------------------------------------------------------------------

Declare @ A datetime
Set @ A = '2017-07-8 13:24:09. 123'
Select @ A as 'current date ',
Datename (year, @ A) as 'Year ',
Datename (month, @ A) as 'month ',
Datename (day, @ A) as 'day ',
Datename (DW, @ A) as 'Week ',
Datename (Week, @ A) as 'Week number ',
Datename (hour, @ A) as 'time ',
Datename (minute, @ A) as 'Min ',
Datename (second, @ A) as 'second'

 

 

--------------------------------------------------------------------------------

Recursively query all days of a year

1.

With dates
(Select [date] = convert (datetime, '2014/1/123 ')
Union all
Select [date] = dateadd (day, 1, [date]) from dates where date <'2014/1/123 ')
Select [date], datename (year, date) as year, datename (month, date) as month, datename (day, date) as day, datename (DW, date) as week, datename (Week, date) as week number from dates option (maxrecursion 366)
2.

With ctedates as (select cast ('000000' as datetime) as dateval Union all select dateadd (DD, 1, dateval) from ctedates where dateval <'000000') Select dateval, 20070101, 0, 0, 0, 0, 0 from ctedates option (maxrecursion 366)

 

Bytes -------------------------------------------------------------------------------------

// Statistics by calendar week
Select to_char (date, 'iw'), sum ()
From
Where
Group by to_char (date, 'iw ')

// Statistics by calendar month
Select to_char (date, 'mm'), sum ()
From
Where
Group by to_char (date, 'mm ')

// Quarterly statistics
Select to_char (date, 'q'), sum ()
From
Where
Group by to_char (date, 'q ')

// Annual Statistics
Select to_char (date, 'yyyy'), sum ()
From
Where
Group by to_char (date, 'yyyy ')
------------------------------------------------------------------------

Datediff Function

Returns the value of variant (long), indicating the number of time intervals between two specified days.

Syntax

Datediff (interval, date1, date2 [, firstdayofweek [, firstweekofyear])

The datediff function syntax contains the following naming parameters:

Partial description
Interval is required. String expression used to calculate the time difference between date1 and date2
Date1 □date2 is required; variant (date ). The two dates used in the calculation.
Firstdayofweek is optional. Specifies the constant of the first day of a week. If not specified, Sunday is the first day.
Firstweekofyear is optional. Specifies the constant of the first week of a year. If this parameter is not specified, the week that contains April 1 is the first week.

Set

The interval parameter settings are as follows:

Set description
Yyyy
Q quarter
M month
Y number of days in a year
D
Number of days per week
WW week
H hour
N minutes
S seconds

The firstdayofweek parameter is set as follows:

Constant Value description
Vbusesystem 0 is set using nls api.
Vbsunday 1 Sunday (default)
Vbmonday 2 Monday
Vbtuesday 3 Tuesday
Vbwednesday 4
Vbthursday 5 Thursday
Vbfriday 6 Friday
Vbsaturday 7 Saturday

Constant Value description
Vbusesystem 0 is set using nls api.
Vbfirstjan1 1 starts from the week of July 1, January 1 (default ).
Vbfirstfourdays 2 starts from the first half of its week in the week of the New Year.
Vbfirstfullweek 3 starts from the week of the first non-Cross-Year Plan.

 

----------------------------------------------------------------------------------

 

Monday of this week: Select dateadd (wk, datediff (wk, 0, getdate (), 0)
First day of a year: Select dateadd (YY, datediff (YY, 0, getdate (), 0)
First day of the quarter: Select dateadd (QQ, datediff (QQ, 0, getdate (), 0)
Last day of the month: Select dateadd (DD,-day (getdate (), dateadd (M, 1, getdate ()))
1st day of last month: Select dateadd (DD,-day (dateadd (month,-1, getdate () + 1, dateadd (month,-1, getdate ()))
Last month's end: Select dateadd (DD,-day (getdate (), getdate () 1st day of this month select dateadd (DD,-day (getdate () + 1, getdate ())
End of this month: Select dateadd (DD,-day (dateadd (month, 1, getdate (), dateadd (month, 1, getdate ()))
1st day of next month: Select dateadd (DD,-day (dateadd (month, 1, getdate () + 1, dateadd (month, 1, getdate ()))
End of next month: Select dateadd (DD,-day (dateadd (month, 2, getdate (), dateadd (month, 2, getdate ()))