SQL Server Date

The number of SQL statements on the first and last day of the month is taken online, either through character interception, or through functions, individuals or rather by using built-in functions to handle,

But look at the use of the online function to take the first day and the last day of the SQL statement almost like the following, in fact, there is a problem, there is a critical issue.

First day of the month: select DATEADD (Dd,-day (getdate ()) +1,getdate ())

Last day of the month: select DATEADD (Dd,-day (getdate ()), DateAdd (M,1,getdate ()))
The above two sentences, the first sentence to take the first day of completely no problem, the second sentence has a problem, the general idea is to take the last day, you can add one months in the current time, and then subtract the current time of days, such as 2009-8-27, plus one months is 2009-9-27, Then subtract 2009-8-27 time 27 days is just 2009-8-31, no problem.

However, if the current time itself is the last day, it will produce a critical problem, such as the actual 2009-5-31, the final date of the last day should be 2009-5-31 only, if according to the above notation, 2009-5-31 plus one months is how much, 2009-6-31? 2009-7-1? are not, because the month Otsuki small problem, June only 30 days, so 2009-5-31 plus one months later is 2009-6-30 day, or according to the above wording and then subtract 2009-5-31 time days 31 days, finally get the last day is 2009-5-30, dumbfounded, what happened ah?

There are only 28 or 29 days in February, of course, there will be such problems, as long as a slight change, in reducing the number of days should not reduce the number of days of the current time, but should be minus the number of days after the month, the following wording:
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, such as 2009-1-30 plus one months is 2009-2-28, minus 28 days after the 2009-1-31 is in line with correctness.

You can, and so forth:

You can, and so forth:

Select DATEADD (Dd,-day (DateAdd (Month,-1,getdate ())) +1,dateadd (Month,-1,getdate ())///Last month */
Select DATEADD (Dd,-day (getdate ()), GETDATE ())/* Last month Bottom */
Select DATEADD (Dd,-day (getdate ()) +1,getdate ())/* Number one this month */
Select DATEADD (Dd,-day (DateAdd (Month,1,getdate ())), DateAdd (Month,1,getdate ())/* * End of Month */
Select DATEADD (Dd,-day (DateAdd (Month,1,getdate ())) +1,dateadd (Month,1,getdate ())/*/next month */
Select DATEADD (Dd,-day (DateAdd (Month,2,getdate ())), DateAdd (Month,2,getdate ())/*//end of Month */

If you want to convert the time format to the "YYYY/MM/DD hh:mi:ss" format, you can:

Select RTrim (CONVERT (Char,getdate (), 111)) + "+ (CONVERT (Char,getdate (), 108))--Yyyy/mm/dd Hh:mi:ss

Test: (list last month start and end time, then convert time format)
DECLARE @time1 datetime,
@time2 datetime,
@time3 datetime,
@time4 datetime

Set @time1 = DateAdd (Dd,-day (DateAdd (Month,-1,getdate ())) +1,dateadd (Month,-1,getdate ())),--Last month
@time2 = DATEADD (Dd,-day (getdate ()) +1,getdate ())--month number one
Set @time3 = RTrim (convert (char, @time1, 111)) + "+ (convert (char, @time1, 108)),
@time4 = RTrim (convert (char, @time2, 111)) + "+ (convert (char, @time2, 108))

Select DATEADD (Dd,-day (DateAdd (Month,-1,getdate ())) +1,dateadd (Month,-1,getdate ()))
Select DATEADD (Dd,-day (getdate ()) +1,getdate ())
Select RTrim (Convert (char, @time1, 111)) + "+ (convert (char, @time1, 108))--Yyyy/mm/dd Hh:mi:ss
Select RTrim (Convert (char, @time2, 111)) + "+ (convert (char, @time2, 108))--Yyyy/mm/dd Hh:mi:ss

Output result: (Sybase)
Jan 1 5:30PM
-
Feb 1 5:30PM
-
2011/01/01 17:30:58
-
2011/02/01 17:30:58

Share from: JUNYIIBM

SQL Server Date