Online collection of date computing in SQL

When it comes to the date and time functions in SQL, we have to come up with the following two concepts: deterministic and non-deterministic functions.

Deterministic function: the obtained value is in a set of established values, or can be expected.
Non-deterministic functions: the results cannot be accurately obtained.

The following describes the types of date functions and time functions based on the two types mentioned above:

1. deterministic functions:
[1] DateAdd ()
Purpose: return the new DateTime value based on the specified date plus a period of time.

Syntax:
DateAdd (datepart, number, date)

Parameters:
Datepart

Is a parameter that specifies which part of the date should be returned to the new value. The following table lists the dates and abbreviations recognized by Microsoft SQL Server.

Number

Is used to increase the value of datepart. If the specified value is not an integer, the fractional part of the value is discarded.
For example, if you specify day for datepart and 1.75 for number, day is increased by 1.

Date
Is an expression that returns a string of the DateTime type, SmallDateTime value, or date format.

Return Value Type:
Return DateTime. If the date parameter is SmallDateTime, return SmallDateTime.

Example:
In this example, the current time plus 21 days is printed,
DateAdd (day, 21, GetDate ())
If the number value is negative and the value is greater than the number of days remaining this month, the function calculates the number by itself.

[2] DateDiff ()
Purpose:
Returns the number of date and time boundaries that span two specified dates.

Syntax:
DateDiff (datepart, startDate, endDate)

Parameters:
Datepart
This parameter is described in DateAdd (). We will not repeat it here, and the following datepart parameters are also the same.

StartDate
Is the calculation start date, and startDate is the expression that returns the DateTime, SmallDateTime value or date format string.
Because SmallDateTime is accurate to minutes, when SmallDateTime is used, the second and millisecond are always 0.

EndDate
Is always calculated by date. EndDate is an expression that returns the DateTime, SmallDateTime, or date format string.

Return Value Type:
Int

Explanation:
StartDate is subtracted from endDate. If startDate is greater than endDate, a negative value is returned.
When the value exceeds the Integer Range, DateDiff produces an error.
The maximum number of milliseconds is 24 days, 20 hours, 31 minutes, And 23.647 seconds.
The maximum number of seconds is 68 years.
The cross-minute, second, and millisecond Boundary Calculation method makes the results given by DateDiff consistent in all data types.
The result is an integer with positive and negative numbers, which is equal to the datepart boundary value across the first and second day periods.
For example, the number of weeks between January 4 (Sunday) and January 11 (Sunday) is 1.

Example:
Select DateDiff (day, '2017-10-24 ', GetDate ())

[3] Day ()
Purpose:
Returns an integer that represents the day of the specified date.

Syntax:
Day (date)

Parameters:
Date
An expression of the DateTime or SmallDateTime type.

Return Value Type:
Int

Explanation:
This function is equivalent to DatePart (dd, date)

Example:
The following example returns day from
Select Day ('2017-10-4 ')
4, that is, the date part.

[4] Month ()
Purpose:
Returns an integer representing the month of the specified date.

Syntax:
Month (date)

Parameters:
Date
Returns the expression of a string of DateTime, SmallDateTime, or date format.

Return Value Type:
Int

Explanation:
This function is equivalent to DatePart (mm, date)

Example:
The following example returns the month part from.
Select Month ('2014-10-4 ')
Returns 10, that is, the month.

[5] Year ()
Purpose:
Returns an integer that represents the year of the specified date.

Syntax:
Year (date)

Parameters:
Date
An expression of the DateTime or SmallDateTime type.

Return Value Type:
Int

Explanation:
This function is equivalent to DatePart (yy, date)

Example:
In the following example, the year score is returned from 2009-10-4.
Select Year ('1970-10-4 ')
Get 2009, that is, the year part

2. Non-deterministic functions:
[1] DateName ()
Purpose:
Returns the string representing the specified date part of the specified date.

Syntax:
DateName (datepart, date)

Parameters:
Datepart
Same as DateAdd ()

Return Value Type:
Nvarchar

Explanation:
SQL Server automatically exchanges character and DateTime values as needed. For example, when the character and DateTime value are compared.

Example:
In this example, the month name is extracted from the date returned by GetDate,
Select DateName (month, GetDate ())
Get October

[2] GetDate ()
Purpose:
Return the current system date and time in the standard internal format of Microsoft SQL Server with DateTime value.

Syntax:
GetDate ()

Return Value Type:
DateTime

Explanation:
The date function can be used in the select statement selection list or in the where clause of the query,
When designing a report, The GetDate function can be used to print the current date and time each time a report is generated.
GetDate is also useful for tracking activities, such as recording the time when a transaction occurs on an account.

Example:
A. Use GetDate to return the current date and time
The following example shows the current system Date and Time:
Select GetDate ()

B. Use GetDate In the create table statement
The following example creates an Employees table and uses GetDate to display the default value of employee employment time.
Use Test
Go

Create table Employees (
Id int identity (1, 1 ),
EmpName varchar (40) not null,
CreateDtm DateTime default GetDate ()
)
Go

[3] GetUtcDate ()
Purpose:
Returns the DateTime value that represents the current UTC time (World Time Coordinate or Greenwich Mean Time.
The current UTC time is obtained from the current local time and the settings in the computer operating system that runs SQL Server.

Syntax:
GetUtcDate ()

Return Value Type:
DateTime

Explanation:
GetUtcDate is a non-deterministic function. Views and expressions that reference this column cannot be indexed.
GetUtcDate cannot be used in user-defined functions.

In the end, this is special. It is DatePart ()
All functions except DatePart (dw, date) are deterministic.
Dw is the date part of the working day.

Purpose:
Returns a function that represents the specified date of a specified date.

Syntax:
DatePart (datepart, date)

Parameters:
Datepart
Here we have an extra weekday (dw) and return the number corresponding to the day of the week,
For example, Sunday = 1, Saturday = 7.
The number of weekday dates depends on the set datefirst value. This command sets the first day of the week.

Date
Is an expression that returns the DateTime, SmallDateTime, or date format string.

Return Value Type:
Int

Explanation:
The functions of Day, Month, and Year are equivalent to DatePart (dd, date), DatePart (mm, date), and DatePart (yy, date)

Example:
The GetDate function returns the current date. However, the comparison does not always require complete date information. It usually only compares a part of the date.
Select DatePart (mm, GetDate () as 'month num'
Go
Get 10