Syntax of DATEADD function
The DATEADD function in SQL Server is used to add or subtract a specified time interval from a given date. The syntax of the DATEADD function is as follows:
DATEADD(datepart, number, date)
Where:
- datepart: Specifies the part of the date to add or subtract. It can be one of the following values:
- year: Adds or subtracts the specified number of years.
- quarter: Adds or subtracts the specified number of quarters.
- month: Adds or subtracts the specified number of months.
- dayofyear: Adds or subtracts the specified number of days to the year.
- day: Adds or subtracts the specified number of days.
- week: Adds or subtracts the specified number of weeks.
- weekday: Adds or subtracts the specified number of weekdays (Monday to Friday).
- hour: Adds or subtracts the specified number of hours.
- minute: Adds or subtracts the specified number of minutes.
- second: Adds or subtracts the specified number of seconds.
- millisecond: Adds or subtracts the specified number of milliseconds.
- microsecond: Adds or subtracts the specified number of microseconds.
- nanosecond: Adds or subtracts the specified number of nanoseconds.
- number: Specifies the number of datepart to add or subtract. It can be a positive or negative integer.
- date: Specifies the date from which to add or subtract the specified datepart.
It is important to note that the DATEADD function does not modify the original date. Instead, it returns a new date with the specified datepart added or subtracted.
Usage of DATEADD function
The DATEADD function is commonly used in SQL Server to perform various date and time calculations. Here are a few examples of how the DATEADD function can be used:
Example 1: Adding days to a date
To add a specific number of days to a given date, you can use the DATEADD function with the ‘day’ datepart. For example:
SELECT DATEADD(day, 7, '2022-01-01') AS NewDate;
This query will return the date that is 7 days after ‘2022-01-01’. The result will be ‘2022-01-08’.
Example 2: Subtracting months from a date
To subtract a specific number of months from a given date, you can use the DATEADD function with the ‘month’ datepart. For example:
SELECT DATEADD(month, -3, '2022-01-01') AS NewDate;
This query will return the date that is 3 months before ‘2022-01-01’. The result will be ‘2021-10-01’.
Example 3: Adding hours to a date
To add a specific number of hours to a given date, you can use the DATEADD function with the ‘hour’ datepart. For example:
SELECT DATEADD(hour, 2, '2022-01-01 10:00:00') AS NewDate;
This query will return the date and time that is 2 hours after ‘2022-01-01 10:00:00’. The result will be ‘2022-01-01 12:00:00’.
Example 4: Subtracting minutes from a date
To subtract a specific number of minutes from a given date, you can use the DATEADD function with the ‘minute’ datepart. For example:
SELECT DATEADD(minute, -30, '2022-01-01 10:00:00') AS NewDate;
This query will return the date and time that is 30 minutes before ‘2022-01-01 10:00:00’. The result will be ‘2022-01-01 09:30:00’.
Example 5: Adding milliseconds to a date
To add a specific number of milliseconds to a given date, you can use the DATEADD function with the ‘millisecond’ datepart. For example:
SELECT DATEADD(millisecond, 500, '2022-01-01 10:00:00') AS NewDate;
This query will return the date and time that is 500 milliseconds after ‘2022-01-01 10:00:00’. The result will be ‘2022-01-01 10:00:00.500’.
These are just a few examples of how the DATEADD function can be used in SQL Server. It is a powerful function that allows you to perform various date and time calculations with ease.
