SQL Server time and date functions

1. dateadd
Syntax
Dateadd (datepart, number, date)
Parameters
Datapart specifies the date component of the new value to be returned.
Commonly used: Year, quarter, month, dayofyear, day, week, weekday
Number is used to add a value to datepart. If a non-integer value is specified, the fractional part of the value is discarded.
Date expression, which is used to return datetime, smalldatetime, or a string in date format.
Return type
Returns datetime. However, if the date parameter is smalldatetime, smalldatetime is returned.
Example
The following example will output a list of order time ranges in the adventureworks database. The time range is the date of the current order plus 21 days.

1 Use Adventureworks;
2 Go
3 Select   Dateadd ( Day , 21 , Orderdate) As Timeframe
4 From Sales. salesorderheader;
5 Go
6
7 Select   Dateadd ( Day , 1 , Getdate ())

 

2. datediff
Syntax
Datediff (datepart, startdate, enddate)
Remarks
Enddate minus startdate. If startdate is later than enddate, a negative value is returned.
Parameters
Datepart
Startdate
Start date of the calculation. Startdate is an expression that returns the datetime, smalldatetime, or date format string.
Enddate
End date of the calculation. Enddate is an expression that returns the datetime, smalldatetime, or date format string.
Return type
Integer

Example:

1 Use Adventureworks;
2 Go
3 Select   Datediff ( Day , Orderdate, Getdate ()) As Numberofdays
4 From Sales. salesorderheader;
5 Go
6
7 Select   Datediff ( Day , ' 2010-8-1 ' , Getdate ())

 

3. datename
Returns the string that represents the specified date part of the specified date.

Syntax
Datename (datepart, date)

Parameters
Datepart
Is a parameter that specifies the date part to be returned.
Date
Returns the datetime or smalldatetime value or a string in the date format.

Return type
Nvarchar

Example

1 Select   Datename ( Month , Getdate ()) As   ' Month name '

 

 

4. datepart
Returns an integer that represents the specified date of a specified date.

Syntax
Datepart (datepart, date)

Remarks
The day, month, and year functions are synonyms of datepart (DD, date), datepart (mm, date), and datepart (YY, date.

Parameters
Datepart
Specify the parameters of the date to be returned.
Date
Returns a string of datetime, smalldatetime, or date format.

Return type
Int

Example

1 Select   Getdate () As   ' Current date '
2 Go