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 DateDiff function is a powerful SQL function you can use to manipulate and work with dates. For example, you may want to find the difference between two dates in the number of days, hours, minutes, etc. The example scripts below will show you how to find specific intervals between dates.
| Unit of time |
Query |
Result |
|
| NANOSECOND |
SELECT DATEDIFF(NANOSECOND,'2018-12-01 17:15:22.5500000','2018-12-01 17:15:22.55432133') |
4321300
|
| MICROSECOND |
SELECT DATEDIFF(MICROSECOND,'2018-12-01 17:15:22.5500000','2018-12-01 17:15:22.55432133') |
4321
|
| MILLISECOND |
SELECT DATEDIFF(MILLISECOND,'2018-12-01 17:15:22.004','2018-12-01 17:15:22.548') |
544
|
| SECOND |
SELECT DATEDIFF(SECOND,'2018-12-01 17:15:30','2018-12-01 17:16:23') |
53
|
| MINUTE |
SELECT DATEDIFF(MINUTE,'2018-12-01 18:03:23','2018-12-01 17:15:30') |
-48
|
| HOUR |
SELECT DATEDIFF(HH,'2018-12-01 18:03:23','2018-12-01 20:15:30') |
2
|
| WEEK |
SELECT DATEDIFF(WK,'09/23/2018 15:00:00','12/11/2018 14:00:00') |
12
|
| DAY |
SELECT DATEDIFF(DD,'09/23/2018 15:00:00','08/02/2018 14:00:00') |
-52
|
| DAYOFYEAR |
SELECT DATEDIFF(DY,'01/01/2018 15:00:00','08/02/2018 14:00:00') |
213
|
| MONTH |
SELECT DATEDIFF(MM,'11/02/2018 15:00:00','01/01/2018 14:00:00') |
-10
|
| QUARTER |
SELECT DATEDIFF(QQ,'01/02/2018 15:00:00','08/01/2018 14:00:00') |
2
|
| YEAR |
SELECT DATEDIFF(YY,'01/02/2013 15:00:00','01/01/2018 14:00:00') |
5 |
Production Review
WSI can adapt this script for your database, improve error handling, tune performance, document the logic, and help deploy it safely.