We can find the difference between the two dates using the following dateparts with the DATEDIFF function. The return data type is int.
If the int data type is insufficient for the difference between two dates, you should use the DATEDIFF_BIG function. You can use it in the same way as DATEDIFF function. Only return data type is bigint.
| datepart | Abbreviations |
| year | yy, yyyy |
| quarter | qq, q |
| month | mm, m |
| dayofyear | dy, y |
| day | dd, d |
| week | wk, ww |
| weekday | dw, w |
| hour | hh |
| minute | mi, n |
| second | ss, s |
| millisecond | ms |
| microsecond | mcs |
| nanosecond | ns |
Let’s make examples for a better understanding of DATEDIFF Function.
Find the month difference between two dates:
|
1 2 3 |
SELECT DATEDIFF(month,'2019-01-01','2022-03-01') AS month_DATEPART, DATEDIFF(m,'2019-01-01','2022-03-01') AS m_DATEPART, DATEDIFF(mm,'2019-01-01','2022-03-01') AS mm_DATEPART |
