DateDiffSQL

Returns an integer value in the same units as the datePart parameter. The return value is the result of subtracting the second date and time from the first date and time. This function behaves the same as the Microsoft SQL function called DATEDIFF.

This function uses the boundary crossing method to calculate the date difference, while the Diff functions (such as DiffDays, DiffMinutes, DiffYears, and so on) use the whole interval method. See Date Functions for more information about using the boundary crossing method versus using the whole interval method.

Syntax

DateDiff(datePart, firstDateTime, secondDateTime, timeZone)

Enabled For

For a description of the business object categories, see Notes on "Enabled For".

Business Object Category Yes/No
Business Rules: Before-Save Rules Yes
Business Rules: Calculation Rules (After Save, with or without Also Recalculate on Load) Yes
Business Rules: Calculation Rules (Before Save or Always, without Also Recalculate On Load) Yes
Business Rules: Calculation Rules (Before Save or Always, with Recalculate On Load) Yes
Business Rules: Editing Rules Yes
Business Rules: Initialization Rules Yes
Business Rules: Read Only Rules Yes
Business Rules: Required Rules Yes
Business Rules: Validation Rules Yes
Client Expressions Yes
Object Permissions Yes
Services Yes
LDAP Yes
Mobile Yes
Quick Actions (except UI Quick Actions) Yes
UI Quick Actions Yes
Reports Yes
Search/Dashboard without field references Yes
Search/Dashboard with field references Yes

Parameters

datePart

The interval type. Must be surrounded by quotes (" "). The following interval types are supported:

millisecond

second

minute

hour

day

week

month

quarter

year

firstDateTime

The first date and time to be compared.

secondDateTime

The second date and time to be compared.

timeZone

(Optional) The time zone, in IANA format.

Return Value

Number value.

If the firstDateTime parameter is before the secondDateTime parameter, the returned value is positive. If the firstDateTime parameter is after the secondDateTime parameter, the returned value is negative. (This is the opposite of the DateDiff function.)

Example

To calculate the weekday of a given date within a quick action, you can use:

$(DateDiffSQL("day", "2012-1-1 12:00 AM", ToString(CurrentDate()) % 7) +1)

This results in 1 for Sunday, 2 for Monday, and so on.