4.9.2. Date: date()

Details for using the date() function.

4.9.2.1. Getting the current Date

date() returns the current Date value. If no time zone parameter is specified, the local time zone will be used.

Syntax: date([ {timezone} ])

Returns:

A Date.

Arguments:

Name Description

A single map consisting of the following:

 

timezone

A string expression that represents the time zone

Considerations:

If no parameters are provided, date() must be invoked (date({}) is invalid).

Query. 

RETURN date() AS currentDate

The current date is returned.

Table 4.91. Result
currentDate

1 row

2019-03-12

Try this query live.  none RETURN date() AS currentDate

Query. 

RETURN date({ timezone: 'America/Los Angeles' }) AS currentDateInLA

The current date in California is returned.

Table 4.92. Result
currentDateInLA

1 row

2019-03-12

Try this query live.  none RETURN date( {timezone: 'America/Los Angeles'} ) AS currentDateInLA

date.transaction()

date.transaction() returns the current Date value using the transaction clock. This value will be the same for each invocation within the same transaction. However, a different value may be produced for different transactions.

Syntax: date.transaction([ {timezone} ])

Returns:

A Date.

Arguments:

Name Description

timezone

A string expression that represents the time zone

Query. 

RETURN date.transaction() AS currentDate

Table 4.93. Result
currentDate

1 row

2019-03-12

Try this query live.  none RETURN date.transaction() AS currentDate

date.statement()

date.statement() returns the current Date value using the statement clock. This value will be the same for each invocation within the same statement. However, a different value may be produced for different statements within the same transaction.

Syntax: date.statement([ {timezone} ])

Returns:

A Date.

Arguments:

Name Description

timezone

A string expression that represents the time zone

Query. 

RETURN date.statement() AS currentDate

Table 4.94. Result
currentDate

1 row

2019-03-12

Try this query live.  none RETURN date.statement() AS currentDate

date.realtime()

date.realtime() returns the current Date value using the realtime clock. This value will be the live clock of the system.

Syntax: date.realtime([ {timezone} ])

Returns:

A Date.

Arguments:

Name Description

timezone

A string expression that represents the time zone

Query. 

RETURN date.realtime() AS currentDate

Table 4.95. Result
currentDate

1 row

2019-03-12

Try this query live.  none RETURN date.realtime() AS currentDate

Query. 

RETURN date.realtime('America/Los Angeles') AS currentDateInLA

Table 4.96. Result
currentDateInLA

1 row

2019-03-12

Try this query live.  none RETURN date.realtime('America/Los Angeles') AS currentDateInLA

4.9.2.2. Creating a calendar (Year-Month-Day) Date

date() returns a Date value with the specified year, month and day component values.

Syntax: date({year [, month, day]})

Returns:

A Date.

Arguments:

Name Description

A single map consisting of the following:

 

year

An expression consisting of at least four digits that specifies the year.

month

An integer between 1 and 12 that specifies the month.

day

An integer between 1 and 31 that specifies the day of the month.

Considerations:

The day of the month component will default to 1 if day is omitted.

The month component will default to 1 if month is omitted.

If month is omitted, day must also be omitted.

Query. 

UNWIND [
date({ year:1984, month:10, day:11 }),
date({ year:1984, month:10 }),
date({ year:1984 })
] AS theDate
RETURN theDate

Table 4.97. Result
theDate

3 rows

1984-10-11

1984-10-01

1984-01-01

Try this query live.  none UNWIND [ date({year:1984, month:10, day:11}), date({year:1984, month:10}), date({year:1984}) ] as theDate RETURN theDate

4.9.2.3. Creating a week (Year-Week-Day) Date

date() returns a Date value with the specified year, week and dayOfWeek component values.

Syntax: date({year [, week, dayOfWeek]})

Returns:

A Date.

Arguments:

Name Description

A single map consisting of the following:

 

year

An expression consisting of at least four digits that specifies the year.

week

An integer between 1 and 53 that specifies the week.

dayOfWeek

An integer between 1 and 7 that specifies the day of the week.

Considerations:

The day of the week component will default to 1 if dayOfWeek is omitted.

The week component will default to 1 if week is omitted.

If week is omitted, dayOfWeek must also be omitted.

Query. 

UNWIND [
date({ year:1984, week:10, dayOfWeek:3 }),
date({ year:1984, week:10 }),
date({ year:1984 })
] AS theDate
RETURN theDate

Table 4.98. Result
theDate

3 rows

1984-03-07

1984-03-05

1984-01-01

Try this query live.  none UNWIND [ date({year:1984, week:10, dayOfWeek:3}), date({year:1984, week:10}), date({year:1984}) ] as theDate RETURN theDate

4.9.2.4. Creating a quarter (Year-Quarter-Day) Date

date() returns a Date value with the specified year, quarter and dayOfQuarter component values.

Syntax: date({year [, quarter, dayOfQuarter]})

Returns:

A Date.

Arguments:

Name Description

A single map consisting of the following:

 

year

An expression consisting of at least four digits that specifies the year.

quarter

An integer between 1 and 4 that specifies the quarter.

dayOfQuarter

An integer between 1 and 92 that specifies the day of the quarter.

Considerations:

The day of the quarter component will default to 1 if dayOfQuarter is omitted.

The quarter component will default to 1 if quarter is omitted.

If quarter is omitted, dayOfQuarter must also be omitted.

Query. 

UNWIND [
date({ year:1984, quarter:3, dayOfQuarter: 45 }),
date({ year:1984, quarter:3 }),
date({ year:1984 })
] AS theDate
RETURN theDate

Table 4.99. Result
theDate

3 rows

1984-08-14

1984-07-01

1984-01-01

Try this query live.  none UNWIND [ date({year:1984, quarter:3, dayOfQuarter: 45}), date({year:1984, quarter:3}), date({year:1984}) ] as theDate RETURN theDate

4.9.2.5. Creating an ordinal (Year-Day) Date

date() returns a Date value with the specified year and ordinalDay component values.

Syntax: date({year [, ordinalDay]})

Returns:

A Date.

Arguments:

Name Description

A single map consisting of the following:

 

year

An expression consisting of at least four digits that specifies the year.

ordinalDay

An integer between 1 and 366 that specifies the ordinal day of the year.

Considerations:

The ordinal day of the year component will default to 1 if ordinalDay is omitted.

Query. 

UNWIND [
date({ year:1984, ordinalDay:202 }),
date({ year:1984 })
] AS theDate
RETURN theDate

The date corresponding to 11 February 1984 is returned.

Table 4.100. Result
theDate

2 rows

1984-07-20

1984-01-01

Try this query live.  none UNWIND [ date({year:1984, ordinalDay:202}), date({year:1984}) ] as theDate RETURN theDate

4.9.2.6. Creating a Date from a string

date() returns the Date value obtained by parsing a string representation of a temporal value.

Syntax: date(temporalValue)

Returns:

A Date.

Arguments:

Name Description

temporalValue

A string representing a temporal value.

Considerations:

temporalValue must comply with the format defined for dates.

date(null) returns the current date.

temporalValue must denote a valid date; i.e. a temporalValue denoting 30 February 2001 is invalid.

Query. 

UNWIND [
date('2015-07-21'),
date('2015-07'),
date('201507'),
date('2015-W30-2'),
date('2015202'),
date('2015')
] AS theDate
RETURN theDate

Table 4.101. Result
theDate

6 rows

2015-07-21

2015-07-01

2015-07-01

2015-07-21

2015-07-21

2015-01-01

Try this query live.  none UNWIND [ date('2015-07-21'), date('2015-07'), date('201507'), date('2015-W30-2'), date('2015202'), date('2015') ] as theDate RETURN theDate

4.9.2.7. Creating a Date using other temporal values as components

date() returns the Date value obtained by selecting and composing components from another temporal value. In essence, this allows a DateTime or LocalDateTime value to be converted to a Date, and for "missing" components to be provided.

Syntax: date({date [, year, month, day, week, dayOfWeek, quarter, dayOfQuarter, ordinalDay]})

Returns:

A Date.

Arguments:

Name Description

A single map consisting of the following:

 

date

A Date value.

year

An expression consisting of at least four digits that specifies the year.

month

An integer between 1 and 12 that specifies the month.

day

An integer between 1 and 31 that specifies the day of the month.

week

An integer between 1 and 53 that specifies the week.

dayOfWeek

An integer between 1 and 7 that specifies the day of the week.

quarter

An integer between 1 and 4 that specifies the quarter.

dayOfQuarter

An integer between 1 and 92 that specifies the day of the quarter.

ordinalDay

An integer between 1 and 366 that specifies the ordinal day of the year.

Considerations:

If any of the optional parameters are provided, these will override the corresponding components of date.

date(dd) may be written instead of date({date: dd}).

Query. 

UNWIND [
date({ year:1984, month:11, day:11 }),
localdatetime({ year:1984, month:11, day:11, hour:12, minute:31, second:14 }),
datetime({ year:1984, month:11, day:11, hour:12, timezone: '+01:00' })
] AS dd
RETURN date({ date: dd }) AS dateOnly,
date({ date: dd, day: 28 }) AS dateDay

Table 4.102. Result
dateOnly dateDay

3 rows

1984-11-11

1984-11-28

1984-11-11

1984-11-28

1984-11-11

1984-11-28

Try this query live.  none UNWIND [ date({year:1984, month:11, day:11}), localdatetime({year:1984, month:11, day:11, hour:12, minute:31, second:14}), datetime({year:1984, month:11, day:11, hour:12, timezone: '+01:00'}) ] as dd RETURN date({date: dd}) AS dateOnly, date({date: dd, day: 28}) AS dateDay

4.9.2.8. Truncating a Date

date.truncate() returns the Date value obtained by truncating a specified temporal instant value at the nearest preceding point in time at the specified component boundary (which is denoted by the truncation unit passed as a parameter to the function). In other words, the Date returned will have all components that are less significant than the specified truncation unit set to their default values.

It is possible to supplement the truncated value by providing a map containing components which are less significant than the truncation unit. This will have the effect of overriding the default values which would otherwise have been set for these less significant components. For example, day — with some value x — may be provided when the truncation unit is year in order to ensure the returned value has the day set to x instead of the default day (which is 1).

Syntax: date.truncate(unit, temporalInstantValue [, mapOfComponents ])

Returns:

A Date.

Arguments:

Name Description

unit

A string expression evaluating to one of the following: {millennium, century, decade, year, weekYear, quarter, month, week, day}.

temporalInstantValue

An expression of one of the following types: {DateTime, LocalDateTime, Date}.

mapOfComponents

An expression evaluating to a map containing components less significant than unit.

Considerations:

Any component that is provided in mapOfComponents must be less significant than unit; i.e. if unit is 'day', mapOfComponents cannot contain information pertaining to a month.

Any component that is not contained in mapOfComponents and which is less significant than unit will be set to its minimal value.

If mapOfComponents is not provided, all components of the returned value which are less significant than unit will be set to their default values.

Query. 

WITH datetime({ year:2017, month:11, day:11, hour:12, minute:31, second:14, nanosecond: 645876123, timezone: '+01:00' }) AS d
RETURN date.truncate('millennium', d) AS truncMillenium,
date.truncate('century', d) AS truncCentury,
date.truncate('decade', d) AS truncDecade,
date.truncate('year', d, { day:5 }) AS truncYear,
date.truncate('weekYear', d) AS truncWeekYear,
date.truncate('quarter', d) AS truncQuarter,
date.truncate('month', d) AS truncMonth,
date.truncate('week', d, { dayOfWeek:2 }) AS truncWeek,
date.truncate('day', d) AS truncDay

Table 4.103. Result
truncMillenium truncCentury truncDecade truncYear truncWeekYear truncQuarter truncMonth truncWeek truncDay

1 row

2000-01-01

2000-01-01

2010-01-01

2017-01-05

2017-01-02

2017-10-01

2017-11-01

2017-11-07

2017-11-11

Try this query live.  none WITH datetime({year:2017, month:11, day:11, hour:12, minute:31, second:14, nanosecond: 645876123, timezone: '+01:00'}) AS d RETURN date.truncate('millennium', d) AS truncMillenium, date.truncate('century', d) AS truncCentury, date.truncate('decade', d) AS truncDecade, date.truncate('year', d, {day:5}) AS truncYear, date.truncate('weekYear', d) AS truncWeekYear, date.truncate('quarter', d) AS truncQuarter, date.truncate('month', d) AS truncMonth, date.truncate('week', d, {dayOfWeek:2}) AS truncWeek, date.truncate('day', d) AS truncDay