Date expressions
Date and datetime expressions are very similar, but they cannot be mixed.
Timezones and format settings for date fields are set at the account or user level.
Example fields
We’ll use these sample fields in the expressions below.
FieldID |
Field type |
Value |
date1 |
Date |
5/29/1970 |
date2 |
Date |
6/3/1970 |
datetime1 |
Datetime |
5/29/1970 3:30 PM |
datetime2 |
Datetime |
5/30/1970 3:30 AM |
Functions
Functions go at the start of the expression.
Function |
Description |
Syntax |
Output |
Output type |
Date |
Creates a date from numbers. |
Date(1970, 5, 29) |
5/29/1970 |
Date |
dateFromText |
Creates a date from text. |
Datefromtext(1970/05/29) |
5/29/1970 |
Date |
Datefromtext |
Creates a date from text |
Datefromtext(1970/05/29)
|
1970/05/29
|
Date |
datetoText |
Converts a date value into text. For example, if the date is 1970/05/29, the different formats of conversion are shown in the next column. |
Date_field.toText("y") Date_field.toText("yy") Date_field.toText("yyy") Date_field.toText("yyyyy") Date_field.toText("m") Date_field.toText("mm") Date_field.toText("mmm") Date_field.toText("mmmm") Date_field.toText("d") Date_field.toText("dd") Date_field.toText("ddd") Date_field.toText("dddd") |
70 70 1970 1970 5 05 May May 29 29 Fri Friday |
Text |
dateTime |
Creates a date and time from numbers. Add a time zone. |
Datetime(1970, 5, 29, 15, 30, 00, “America/Los_Angeles”) |
5/29/1970 3:30 PM PST |
Datetime |
dateTimeFromText |
Creates a date and time from text. |
DateTimeFromtText(2021-10-18T11:06:35-08:00 America/Los_Angeles) Note: Inside the integration configuration forms, these are to be formatted as DateTimeFromText(2021-10-18T11:06:35-08:00 America/Los_Angeles) |
2021-10-18T11:06:35-08:00 America/Los_Angeles Note: In integration, the output will be 2021-10-18T11:06:35-08:00 America/Los_Angeles |
DateTime |
dateTimetoText |
Converts a date time value into text. For example, if the date time is 1970/05/29, 14:07:00, the different formats of conversion are shown in the next column. |
Datetime_field.toText("h") Datetime_field.toText("hh") Datetime_field.toText("s") Datetime_field.toText("ss") Datetime_field.toText("am/pm") Datetime_field.toText("a/p") Datetime_field.toText("a") Datetime_field.toText("p") Datetime_field.toText("h:m") Datetime_field.toText("h:m:s") Datetime_field.toText("hh:mm") Datetime_field.toText("hh:mm:ss") |
2 14 00 00 PM PM PM PM 02:07 02:07:00 14:07 14:07:00 |
Text |
Datediff |
Shows the absolute value of the difference between two dates based on a unit. “Year” “Month” “Day” “Hour” “Minute” “Second” It will ignore non-working days by default. To count these, add false(). |
Datediff(date1, date2, “Day”) Datediff(date1, date2, “Day”, false()) |
3 5 |
Number |
Now |
The date and time of the last time the form was modified, based on UTC. |
Now() |
[the present date and time] |
Datetime |
Today |
The present day, based on UTC. |
Today() |
[the present date] |
Date |
Initiatedat |
The date and time when the item was created as a draft, it is displayed in the user’s set time zone. |
Initiatedat() |
[the date and time the item was created as a draft] |
Datetime |
Note:
Now and Today will change whenever the form is modified or submitted. It works exactly like _modified_at.
Methods
Function |
Description |
Syntax |
Output |
Output type |
offset |
Offsets the date and time. Y=year M=month D=day h=hour m=minutes=second |
datetime1.offset(10,”D”) datetime1.offset(-10,”m”) |
06/08/1970 3:30 PM 05/29/1970 3:20 PM |
Date or datetime |
eom |
Shows the last day of the month for the given field. |
datetime1.eom() |
05/31/1970 12:00 AM |
Date or datetime |
weekNum |
Shows the week count for the given field. 1=Sunday 2=Monday, etc. You can change the default start day of the week. |
datetime1.weekNum() |
22 |
Number |
weekDay |
Shows the weekday count for the given field. 1=Sunday 2=Monday, etc. You can change the default start day of the week. |
datetime1.weekDay() datetime1.weekDay(2) |
6 5 |
Number |
year |
Shows the year of the given field. |
datetime1.year() |
1970 |
Number |
month |
Shows the month of the given field. |
datetime1.month() |
5 |
Number |
day |
Shows the day of the month of the given field. |
datetime1.day() |
29 |
Number |
hour |
Shows the hour of the given field. |
datetime1.hour() |
15 |
Number |
minute |
Shows the minute of the given field. |
datetime1.minute() |
30 |
Number |
second |
Shows the seconds of the given field. |
datetime1.second() |
0 |
Number |
Operators
You can use the addition and subtraction operators with date and datetime fields.
Field 1 |
Field 2 |
Operator |
Example |
Result |
Resulting type |
Date |
Number/Integer |
+, - |
date1 + 5 |
6/3/1970 |
Date |
Date |
Date |
- |
date2 - date1 |
5 |
Number |
Datetime1 |
Number/Integer |
+, - |
datetime1 - 1 |
5/28/1970 3:30 PM |
Datetime |
Datetime |
Datetime |
- |
datetime2 - datetime1 |
0.5 |
Number |
Integers represent 24 hours with a datetime field. If you want to subtract 12 hours, enter datetime1-0.5.