Date Functions for Formulas

In Salesforce Data Pipelines, use date functions to adjust or calculate values from dates in your recipe. For example, you can add time, find time until, and extract a day of the week from date fields. The arguments for date functions are date columns.

Date Functions for Formulas

When entering a formula use the API name of the column in the expression, not the label. The parameters section is surrounded by parentheses. For example, you can enter the following expression.

datediff(ClosedDate, CreatedDate)

Date Function	Description	Syntax
`add_months`	Returns the date with the specified number of months after the start date.	`add_months(startDate,num_months)` `startDate` is the date field calculated on. `num_months` is the number of months added to the startDate. This function doesn't adjust the resulting date to a last day of month if the `startDate` is a last day of months. The resulting day of the month is the minimum of original date's day or the last day of the month of the resulting date. For example: `select add_months(DATE'2023-02-28', 1)` returns `2023-03-28`. `select add_months(DATE'2023-01-31', 1)` returns `2023-02-28`. `select add_months(DATE'2024-01-31', 1)` returns `2024-02-29`, as 2020 is a leap year.
`current_date`	Returns the current date.	`current_date()`
`current_timestamp`	Returns the current date and time.	`current_timestamp()`
`datediff`	Returns the number of days between start date and end date.	`datediff(endDate,startDate)` `endDate` is the later of the two dates you are finding the difference between. `startDate` is the earlier of the two dates you are finding the difference between.
`date_add`	Returns the date with the specified number of days after the start date.	`date_add(startDate,num_days)` `startDate` is the date field calculated on. `num_days` is the number of days added to the startDate.
`date_format`	Converts the timestamp to a specified date format.	`date_format(field,'format')` `field` is a date field/timestamp or string to be converted to the given format. `'format'` is the date and time format pattern to follow. Example: `date_format('2024-04-08', 'y')` returns 2024
`date_sub`	Removes the specified number of days from the start date.	`date_sub(startDate,num_days)` `startDate` is the date field calculated on. `num_days` is the number of days subtracted from the startDate.
`date_trunc`	Resets the specified part of the timestamp, and all portions after it, to zero (or 01 for date and year). Specify the portion of the timestamp, fmt, with ‘year’, ‘quarter’, ‘month’, ‘week’, ‘day’, ‘hour’, ‘minute’, or ‘second’. For example, dateTrunc for ‘year’ of 2019-05-01 12:31:32 returns 2019-01-01 00:00:00. If your data is date and not datetime, use the trunc function instead.	`date_trunc('format',timestamp)` `'format'` is the date and time format pattern to follow. `timestamp` is a date/timestamp or string that’s returned as a UNIX timestamp. Example: `date_trunc('week',CloseDate)` returns the Monday of the week of the Close Date.
`day`	Returns the day of the month component of the date/timestamp.	`day(date)` `date` is the date field to extract the day of the month from.
`dayofmonth`	Returns the day of the month component of the date/timestamp.	`dayofmonth(date)` `date` is the date field to extract the day of the month from.
`dayofweek`	Returns the day of the week component of the date/timestamp. 1 = Sunday, 2 = Monday, ..., 7 = Saturday.	`dayofweek(date)` `date` is the date field to extract the day of the week from.
`dayofyear`	Returns the day of year component of the date/timestamp.	`dayofyear(date)` `date` is the date field to extract the day of the year from.
`months_between`	Returns the number of months between two timestamps. Calculates the difference based on 31 days per month rounded to 8 digits unless roundOff=false. If timestamp1 is later than timestamp2, the result is positive. If timestamp1 and timestamp2 are on the same day of the month, or both are the last day of the month, the result is an integer and the time of day is ignored.	`months_between(timestamp2,timestamp1,roundOff(optional))` `timestamp2` is one of the timestamps to compare. `timestamp1` is the other timestamp to compare. `roundOff(optional)` is a boolean that determines whether to round the result. This parameter is optional.
`now`	Returns the current date and time in the specified format.	`now()`
`to_date`	Converts the date string to the specified date format. Returns null with invalid input.	`to_date(field,'format(optional)')` `field` is a date string field you want to convert. `'format(optional)'` is the date and time format pattern to follow. This parameter is optional. The function follows casting rules to a date if format isn’t included.
`to_timestamp`	Converts the timestamp string to the specified timestamp format.	`to_timestamp(field,'format(optional)')` `field` is a timestamp string field you want to convert. `'format(optional)'` is the timestamp format pattern to follow. This parameter is optional. The function follows casting rules to a timestamp if format isn’t included.
`to_unix_timestamp`	Returns the UNIX timestamp of the specified time.	`to_unix_timestamp(timestamp,format(optional))` `timestamp` is a date/timestamp or string that’s returned as a UNIX timestamp. `format(optional)` is the date/time format pattern to follow. `format(optional)` is ignored if `timestamp` is not a string. Default value is "yyyy-MM-dd HH:mm:ss". This parameter is optional. Example: `to_unix_timestamp('2016-04-08', 'yyyy-MM-dd')` returns `1460098800`
`trunc`	Replaces the specified portion of the timestamp, and all portions after, with zeroes.	`trunc(field,'format(optional)')` `field` is a timestamp field. `'format(optional)'` specifies the part of the timestamp to truncate. Accepted values are "YEAR", "YYYY", "YY", "MON", "MONTH", "MM", "DAY", "DD", "HOUR", "MINUTE", "SECOND", "WEEK", and "QUARTER". Example: `trunc('2024-03-05T09:32:05.359', 'MM')` returns `2024-03-01T00:00:00`

Keep these behaviors in mind when working with date and time functions.

These functions are relative to when the job runs.
- now
- current_date
- current_timestamp
- to_unix_timestamp
All date and datetime functions are based on the UTC time zone.
When Custom Time Zone Support is enabled, date functions and date-time functions are separated. When it’s disabled, the functions are grouped together.

Did this article solve your issue?

Let us know so we can improve!

Date Functions for Formulas