SQL Server various date calculation methods bis

Source: Internet
Author: User
Tags date functions getdate sql time and seconds time interval

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))



Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.