Several commonly used date functions of SQL Server (2)

in the previous section of SQL Server's commonly used date functions (1), we introduced the first two date functions in the table below. In this section, we will continue to learn the following three functions.

function	description
getdate ()	returns the current date and time
convert ()	display date/time in different formats
datediff ()	returns the time between two dates
datepart ()	Returns a separate part of the date/time
dateadd ()	add or subtract a specified interval from a date

3. datediff (datepart, startdate, enddate) Function

• This function is used to calculate the day (year, month…) between two dates ...) Number, enddate minus startdate. If startdate is later than enddate, a negative value is returned. Startdate indicates the start time, enddate indicates the end time, And datepart indicates the calculation unit of the time interval, such as year, month, and day.DatepartThe following table lists the common parameters:
• Datepart table:

DepartValue	Description
YY, yyyy	Year
QQ, Q	Quarter
Mm, m	Month
Dy, y	Day of the year
DD, d	Day
Wk, WW	Week of the year
DW, W	The day of the week, starting from Sunday.
HH	Hours
Mi, n	Minutes
SS, S	Seconds
MS	Millisecond
MCS	Subtle
NS	Nanoseconds

• Instance: Search for users registered in the last week

Select * From userinfo

Where datediff (day, regtime, getdate () between 0 and 7

4. SQL Server datepart (Datepart,Date) Function

• This function is used to return a separate part of the date/time, such as year, month, day, hour, and minute. WhereDateThe parameter is a valid date expression;DatepartThe parameters are shown in the table above.
• Instance: view the registered year, month, and day of Cathy

Select datepart (yyyy, regtime) as year, datepart (mm, regtime) as month, datepart (DD, regtime) as date
From userinfo
Where susername = 'Cathy'

5. SQL Server dateadd (Datepart,Number,Date) Function

• This function can calculate the time between adding or subtracting the corresponding time interval. WhereDateA parameter is a valid date expression.NumberIs the number of intervals you want to add. If you specify a decimal value, the decimal number is truncated without rounding. For future time, this number is a positive number. For past time, this number is a negative number. For the datepart parameter, see the list in datediff.
• Instance:

Declare @ A Date

Set @ A = getdate ()

Select dateadd (day, 3, @)

Select dateadd (day, 9, @)

6. Quarterly query instances:

 

Declare @ Date Datetime
Set @ Date = Getdate ()
-- At the beginning of the quarter, calculate the first day of the quarter where the given date is located
Select Dateadd (Quarter, Datediff (Quarter, 0 , @ Date ), 0 ) As ' The first day of the current quarter '
-- Calculate the last day of the quarter of the given date at the end of the quarter
Select Dateadd (Quarter, 1 + Datediff (Quarter, 0 , @ Date ), - 1 ) As ' Last day of the current quarter '
-- Beginning of last quarter
Select Dateadd (Quarter, Datediff (Quarter, 0 , @ Date ) - 1 , 0 ) As ' The beginning of the last quarter of the current quarter '
-- Last quarter end
Select Dateadd (Quarter, Datediff (Quarter, 0 , @ Date ), - 1 ) As ' Last quarter of the current quarter '
-- Early next quarter
Select Dateadd (Quarter, 1 + Datediff (Quarter, 0 , @ Date ), 0 ) As ' The beginning of the next quarter of the current quarter '
-- End of next quarter
Select Dateadd (Quarter, 2 + Datediff (Quarter, 0 , @ Date ), - 1 ) As ' End of the next quarter '