Modifying Date and Time Values in SQL Server

SQL Server provides many functions for modifying date and time values. This article explains the following functions to modify date and time values:

1. DATEADD Function
2. EMONTH Function
3. TODATETIMEOFFSET Function

So let's have a look at a practical example of how to get the date and time parts from a DateTime in SQL Server. The example is developed in SQL Server using the SQL Server Management Studio.

DATEADD Function

The DATEADD function is used to do date and time operations. The DATEADD function requires three arguments. The syntax of the DATEADD built-in date function is as follows:

DATEADD (<Part of date>, <Number>, <Date>)

All three parameters of the DATEADD function are required. The <Part of date> parameter is the part of the date parameter to which an integer number is added. The <number> is an expression that can be resolved to an INT data type that is added to a <datepart> of the <date> parameter.

The DATEADD Function For Adding Days

The GetDate function will return the current date.

Example

--Current Datetime

Select GETDATE()as[CurrentDate]

Go

--To add 10 days to date and time the function would be:

SELECTDATEADD(DAY,10,GETDATE())AS [10 days adding with Date]

 

Output

Using the DATEADD Function with an existing date and time value

SELECTDATEADD(DAY,10, '12/5/2009')AS [10 days adding with Date]

Output

The DATEADD Function to Subtract Days

SELECTDATEADD(DAY,-10,GETDATE())AS [10 days subtracting from current Date]

 

Output

The Eomonth Function

The Eomonth function returns the last day of the month that contains the specified date.

Syntax

The syntax of the "Month" built-in date function is as follows :

MONTH ( startdate [,month_to_add ] )

Here,

The "startdate" parameter can be an expression specifying the date for which to return the last day of the month.

The "month_to_add" is optional.

Example

Select getdate()asCurrentDate

Go

SelectEomonth(getdate())asMonth

Go

SelectEomonth('09/12/2012',2)as Month

Go

SelectEomonth('09/12/2012')asMonth

 

Output

TODATETIMEOFFSET Function

If the given date and time value is a DATETIMEOFFSET, the TODATETIMEOFFSET function changes the DATETIMEOFFSET value based on the same original local date and time value plus the new given time zone offset.

Syntax

The syntax of the "TODATETIMEOFFSET" built-in date function is as follows:

TODATETIMEOFFSET (expression, time_zone) 

Here,

expression: The expression cannot be of type text, ntext, or image because these types cannot be implicitly converted to varchar or nvarchar.
time_zone: Is an expression that represents the time zone offset in minutes (if an integer), for example -120, or hours and minutes (if a string).

Example

DECLARE @DateTime datetime2;

SET @DateTime = GETDATE();

SELECTTODATETIMEOFFSET(@DateTime,'-05:00');

 

Output