Date Functions for Formulas

Use date functions to adjust or calculate values from dates. 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 fields.

When entering a formula, use the API name of the field 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`.
`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. Only static integer values are accepted.
`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('2016-04-08', 'y')` returns 2016
`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. Only static integer values are accepted.
`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 which is 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 that 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 that 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. For example, `'M'` or `'L'` is the month number in a year starting from 1. `to_timestamp('2020-01-01', 'M')` returns `1` The value YYYY/MM/DD is a 4-digit year, 2 digit month, and day of the month. `TO_TIMESTAMP('2024/12/10 11:10:15', 'YYYY/MM/DD HH:MI:SS')` returns `10-DEC-24 11.10.15.000000000 AM`
`to_unix_timestamp`	Returns the UNIX timestamp of the specified time.	`to_unix_timestamp(timestamp,format(optional)` `timestamp` is a date/timestamp or string which is 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('2015-03-05T09:32:05.359', 'MM')` returns `2015-03-01T00:00:00`

Date and Time Function Considerations

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

These functions are relative to when the batch data transform 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 DateTime functions are separated. When it’s disabled, the functions are grouped together.