DateDiff() mvSQL database function in Jitterbit App Builder
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 |