SQL gets first day, last day

Interviewed a company yesterday, asked to get the last order of the month on the machine topic. Using the date of the selective query, came back on the Iteye found this article.

Original: http://new-fighter.iteye.com/blog/1758723

① First day of the month (-minus today plus 1 days)

SQL code

SELECT DATEADD (dd,-day(GETDATE ()) +1,getdate ())

② last day of the month

SQL code

SELECT DATEADD (dd,-day(DATEADD (M,1,getdate ())), DATEADD (M,1,getdate () ))

remark:
last day of the month: select DATEADD (Dd,-day (getdate ()), DateAdd (M,1,getdate ()))
The above sentence has a problem, the general idea is to take the last day, you can add one months to the current time, and then subtract the current
the number of days, such as 2009-8-27, plus one months is 2009-9-27, and then subtract 2009-8-27 time of days 27 days is exactly 2009-8-31, no problem. However,
if the current time itself is the last day, there will be a critical problem, such as the actual 2009-5-31, the final date of the last day should actually be
2009-5-31 is right, if according to the above wording, 2009-5-31 plus one months is how much, 2009-6-31? 2009-7-1? No, because the month Otsuki small problem, June only
30 days, so 2009-5-31 plus one months after the 2009-6-30 day, or according to the above notation and then subtract the 2009-5-31 time of days 31 days, the final day is
2009-5-30, dumbfounded, what happened? and February only 28 or 29 days of course there will be problems, as long as a slight change, in reducing the number of days should not reduce the current time
, minus the number of days after the month is added, as follows:

SQL code

Select DATEADD (dd,-day(DateAdd (M,1,getdate ())), DateAdd (M,1,getdate () ))

In this case, even if June does not have 31 days, 2009-6-30 minus 30 days is 2009-5-31, and then 2009-1-30 plus one months is 2009-2-28, minus 28 days after the 2009-1-31 meets
Correctness.

③ Monday of the week　

SQL code

SELECT DATEADD (wk, DATEDIFF (Wk,0,getdate ()), 0)

last day of the Week (Sunday)

SQL code

Select DateAdd (wk, (Select DATEDIFF (Wk,0,getdate ())), 6)

④ The first day of the year
The first day of the year is now displayed with a time interval of year (yy).

SQL code

SELECT DATEADD (yy, DATEDIFF (Yy,0,getdate ()), 0)

first day of the ⑤ quarter
If you want to calculate the first day of the quarter, this example tells you how to do it.

SQL code

SELECT DATEADD (QQ, DATEDIFF (Qq,0,getdate ()), 0)

the last day of 61 years
The last day of the year is now displayed with a time interval of year (yy). It's easy to figure out first day of the year,
SELECT DATEADD (yy, DATEDIFF (Yy,0,getdate ()), 0), and then one year minus a day is the last day of the year

SQL code

SELECT DATEADD (Dd,-1,dateadd (Yy,1,dateadd (Yy,datediff (Yy,0,getdate ()), 0)))

Instance: The first and last day of this month is the last day of the month as of 25th. (Last month 26 to this month)

SQL code

DECLARE @MonthBaseDate DateTime;

DECLARE @MonthBeginDate DateTime;

DECLARE @MonthEndDate DateTime;

SET @MonthBaseDate =getdate ();

IF (Day(@MonthBaseDate) >=26)

SET @MonthBaseDat =dateadd (dd,6, @MonthBaseDate);

Select @MonthBeginDate = DATEADD (dd,25, DATEADD (Mm,datediff (Mm,0,dateadd ())), 0), @MonthEndDate = DATEADD (Dd,24,dateadd (mm, DATEDIFF (Mm,0,getdate ()), 0))

SQL gets first day, last day