mvSQL Database Function - DATEDIFF()¶
Summary¶
Calculates the difference between two date or datetime expressions based on a given date part.
Syntax¶
DATEDIFF(<datepart>, <startdate>, <enddate>)
Parameters¶
datepart¶
| Date Part | Aliases | Vendor Support |
|---|---|---|
year | yy, yyyy | All RDBMS vendors. |
quarter | qq, q | SQL Server and SQLite |
month | mm, m | All RDBMS vendors. |
dayofyear | dy, y | SQL Server and SQLite |
day | dd, d | All RDBMS vendors. |
week | wk, ww | SQL Server and SQLite |
hour | hh | All RDBMS vendors. |
minute | mi, n | All RDBMS vendors. |
second | ss, s | All RDBMS vendors. |
The date part must not be quoted.
The date part is not case-sensitive.
startdate¶
Column or expression of type datetime or date. Some date parts expect a datetime.
enddate¶
Column or expression of type datetime or date. Some date parts expect a datetime.
Returns¶
Returns an int representing the difference between the start date and end date in the appropriate date part.
Remarks¶
Each database vendor may handle date boundaries differently. For example, SQL Server calculates weeks using Sunday as the start of the week. SQLite, on the other hand, uses Monday.
Examples¶
Note that these examples assume SQL Server.
| Expression | Returns |
|---|---|
DATEDIFF(year, TIMESTAMP('2005-12-31 23:59:59.9999999'), TIMESTAMP('2006-01-01 00:00:00.0000000')) | 1 |
DATEDIFF(month, TIMESTAMP('2005-12-31 23:59:59.9999999'), TIMESTAMP('2006-01-01 00:00:00.0000000')) | 1 |