SQL Server various date calculation methods bis

Last day of last month

This is an example that calculates the last day of the last month. It is obtained by subtracting 3 milliseconds from the example on the last day of one months. One thing to keep in mind is that the time in SQL Server is accurate to 3 milliseconds. That's why I need to subtract 3 milliseconds to get the date and time I want.

SELECT DateAdd (Ms,-3,dateadd (mm, DATEDIFF (Mm,0,getdate ()), 0))

The time portion of the calculated date contains a single day that SQL Server can record The time of the last moment ("23:59:59:997").

The last day of last year

joins the example above, in order to get the last day of last year, you need to subtract 3 milliseconds from the first sky of the year.

SELECT DateAdd (Ms,-3,dateadd (yy, DATEDIFF (Yy,0,getdate ()), 0)

The last day of the month

now, to get the last day of the month, I need to revise a little bit to get the last day of last month's statement. Modifications need to be added 1 to the time interval between the current date and the "1900-01-01" comparison with DateDiff. By adding 1 months, I figure out the first day of the next month, then subtract 3 milliseconds, so I can figure out the last day of the month. This is the SQL script that calculates the last day of the month.

SELECT DateAdd (Ms,-3,dateadd (mm, DATEDIFF (M,0,getdate ()) +1, 0)

Last day of the year

you should now master this practice, which is to calculate the last day of the year script

SELECT dateadd (Ms,-3,dateadd (yy, DATEDIFF (Yy,0,getdate ()) +1, 0).

This month's first Monday

Okay, now is the last example. Here I want to calculate the first Monday of this month. This is the computed script.

Select DATEADD (wk, DATEDIFF (wk,0, DATEADD (Dd,6-datepart (Day,getdate ()), GETDATE ()) ), 0)

　　
In this example, I used the "This week's Monday" script and made a little change. Part of the change is to replace the "getdate ()" section of the original script with the 6th day of the month, replacing the current date with the 6th day of the month so that the calculation gets the first Monday of the month.
　　
Summarize
　　
I hope these examples will give you a little bit of inspiration when you use the DATEADD and DATEDIFF functions to calculate dates. By using this mathematical method of calculating the time interval of the date, I found it valuable to show the useful calendars between the two dates. Note that this is just one way to figure out these dates. Keep in mind that there are many ways to get the same results. If you have other methods, that's great, and if you don't, I hope these examples will give you some inspiration when you want to use the DATEADD and DATEDIFF functions to calculate the dates that your program might use.

Appendix: Other Date processing methods
　　
1 Remove time and seconds

DECLARE @ datetime SET @ = getdate ()-' 2003-7-1 10:00:00 ' SELECT @,dateadd (Day, DATEDIFF (day,0,@), 0)

　
2 shows the day of the week

Select Datename (Weekday,getdate ())

3 How to get the number of days in a month

DECLARE @m int Set @m=2--month Select DateDiff (Day, ' 2003-' +cast (@m as varchar) + ' -15 ', ' 2003-' +cast (@m+1 as varchar) + '-15 ') Also, get the number of days this month Select DateDiff (Day,cast (Month (GetDate ()) as varchar) + '-' +cast (Month (GetDate ()) as varchar) + ' -15 ', cast (month ( GetDate ()) as varchar) + '-' +cast (Month (GetDate ()) +1 as varchar) + '-15 ') Or use the script that calculates the last day of the month, and then use the day function area last SELECT Day (DateAdd (Ms,-3,dateadd (mm, DATEDIFF (M,0,getdate ()) +1, 0))

4) to determine whether leap year:

SELECT Case Day (DATEADD (mm, 2, DateAdd (Ms,-3,dateadd (yy, DATEDIFF (Yy,0,getdate ()), 0)) when then ' excepting ' else ' leap Year ' end Or Select Case DateDiff (Day,datename (Year,getdate ()) + ' -02-01 ', DateAdd (Mm,1,datename (Year,getdate ()) + '-02-01 ') When then ' excepting ' else ' leap Year ' end

　　
5) How many days a quarter
　

Declare @m tinyint, @time smalldatetime Select @m=month (getdate ()) Select @m=case when @m between 1 and 3 then 1 When @m between 4 and 6 then 4 When @m between 7 and 9 then 7 Else Ten End Select @time =datename (year,getdate ()) + '-' +convert (varchar (), @m) + '-01 ' Select DateDiff (Day, @time, DateAdd (mm,3, @time))