Skip to Content

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