Example and Notes
Use this SQL Server example as a starting point for development, troubleshooting, reporting, or maintenance work. Review it against your schema, data volume, permissions, and SQL Server version before using it in production.
The DateAdd function is a powerful SQL function you can use to manipulate and work with dates. For example, you may want to add a number of days, hours, minutes, etc. to a specific date. The example scripts below will show you how to add specific intervals to a date.
Examples of Using the DATEADD Function
DECLARE @Date datetime2 = '2018-09-23 15:48:39.2370000'
|
| Unit of time |
Query |
Result |
| NANOSECOND |
SELECT DATEADD(NANOSECOND,150000,@Date) |
2018-09-23 15:48:39.2371500 |
| MICROSECOND |
SELECT DATEADD(MICROSECOND,150000,@Date) |
2018-09-23 15:48:39.3870000 |
| MILLISECOND |
SELECT DATEADD(MILLISECOND,1500,@Date) |
2018-09-23 15:48:40.7370000 |
| SECOND |
SELECT DATEADD(SECOND,1500,@Date) |
2018-09-23 16:13:39.2370000 |
| MINUTE |
SELECT DATEADD(MINUTE,15,@Date) |
2018-09-23 16:03:39.2370000 |
| HOUR |
SELECT DATEADD(HOUR,-3,@Date) |
2018-09-23 12:48:39.2370000 |
| WEEKDAY |
SELECT DATEADD(WEEKDAY,-2,@Date) |
2018-09-21 15:48:39.2370000 |
| WEEK |
SELECT DATEADD(WEEK,4,@Date) |
2018-10-21 15:48:39.2370000 |
| DAY |
SELECT DATEADD(DD,4,@Date) |
2018-09-27 15:48:39.2370000 |
| DAYOFYEAR |
SELECT DATEADD(DY,14,@Date) |
2018-10-07 15:48:39.2370000 |
| MONTH |
SELECT DATEADD(MM,-4,@Date) |
2018-05-23 15:48:39.2370000 |
| QUARTER |
SELECT DATEADD(Q,-2,@Date) |
2018-03-23 15:48:39.2370000 |
YEAR
|
SELECT DATEADD(YYYY,-5,@Date) |
2006-09-23 15:48:39.2370000 |
Production Review
WSI can adapt this script for your database, improve error handling, tune performance, document the logic, and help deploy it safely.