SQL Server processing time Summary

In the development of database applications, processing time problems are often encountered, such as querying records at a specified time. Next we will discuss these common problems with your own experience.
First, we will introduce the usage of several major functions for processing time in SQL Server:
Getdate () function: gets the current date and time of the system. The return value is of the datetime type.
Usage: getdate ()
Example:
Select getdate () as DTE, dateadd (day,-1, getdate () as nowdat
Output result:
DTE nowdat

19:13:10. 083 19:13:10. 083

(1 row (s) affected)

Datepart () function: returns the specified part of the time in integer format.
Usage: datepart (datepart, date)
Parameter description: the part of the time to be returned for datepart. values include year, month, day, hour, and minute.
Date is the specified time.

Example:
Select datepart (month, getdate () as 'month number'
Output result:
Month number

11

(1 row (s) affected)

Dateadd () function: adds an integer to the specified part of the specified time to return a new time value.
Usage: dateadd (datepart, number, date)
Parameter description: datepart (same as above)
Date (same as above)
The value to be added by number. It is an integer that can be positive or negative. A positive value returns the time value after date, and a negative value returns the date.
Previous Time Value

Example:
Select getdate () as today
Select dateadd (day,-1, getdate ())
Select dateadd (day, 1, getdate ())
Output:
Today

19:42:41. 410

(1 row (s) affected)

Yesterday

2017-11-20 19:42:41. 410

(1 row (s) affected)

Tomorrow

19:42:41. 410

(1 row (s) affected)

Datediff () function: returns the difference between two times in the specified time range. Returns an integer. For example, between and
The difference is calculated as nine days. The difference is calculated as one year from to, and the difference is calculated as nine months from.
Usage: datediff (darepart, date1, date2)
Parameter description: datepart (same as above)
Date1 and date2 (same as date)
Example:
Select datediff (month, '2017-6-12 ', '2017-6-21') as
Output:
A

12

(1 row (s) affected)

1) Remove the hour, minute, and second
Declare @ datetime
Set @ = getdate () -- '2017-7-1 10:00:00'
Select @, dateadd (day, datediff (day, 0, @), 0)

2) display the day of the week
Select datename (weekday, getdate ())

3) how to obtain the number of days in a month
Declare @ M int
Set @ M = 2 -- month
Select datediff (day, '2017-'+ Cast (@ M as varchar) +'-15', '2017-'+ Cast (@ m + 1 as varchar) + '-15 ')
In addition, 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 to calculate the last day of the month, and then use the day function area for the last day.
Select Day (dateadd (MS,-3, dateadd (mm, datediff (M, 0, getdate () + 1, 0 )))

4) determine whether a leap year is used:
Select case day (dateadd (mm, 2, dateadd (MS,-3, dateadd (YY, datediff (YY, 0, getdate (), 0 )))) when 28 then 'Year' else 'Year' end
Or
Select case datediff (day, datename (year, getdate () + '-02-01', dateadd (mm, 1, datename (year, getdate ()) + '-02-01 '))
When 28 then 'Year' else 'Year' end

5) How many days of 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 10 end
Select @ time = datename (year, getdate () + '-' + convert (varchar (10), @ M) + '-01'
Select datediff (day, @ time, dateadd (mm, 3, @ time ))