Date expressions

Updated 3 weeks ago by Neil

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

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

Datetime

Creates a date and time from numbers.

Add a time zone.

Datetime(1970, 5, 29, 15, 30, 00, “Los Angeles”)

5/29/1970 3:30 PM Los Angeles

Datetime

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 present date and time, based on UTC.

Now()

[the present date and time]

Datetime

Today

The present day, based on UTC.

Today()

[the present date]

Date

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

week

Shows the week count for the given field.

datetime1.week()

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 additon and subtraction operators with date and datetime fields. 

Field 1

Field 2

Operator

Resulting type

Date

Number/Integer

+, -

Date

Date

Date

-

Number1

Datetime2

Number/Integer

+, -

Datetime

Datetime

Datetime

-

Number

1. Date2-date1 = 5.

2. Integers represent 24 hours with a datetime field. If you want to subtract 12 hours, enter datetime1-0.5.


How did we do?