Date
DATE
Converts a year, month, and day into a date.
Syntax
DATE(year, month, day)
- year: The year component of the date.
- month: The month component of the date.
- day: The day component of the date.
Sample Usage
DATE(1969, 7, 20)
DATE(year, 5, 28)
Notes
- DATE will silently recalculate numeric dates which fall outside of valid month or day ranges. For example, DATE(1969, 13, 1), which specifies the illegal month 13, will create a date of 1/1/1970. Similarly, DATE(1969, 1, 32), which specifies the non-existent 32nd day of January, will create a date of 2/1/1969.
- DATE will silently truncate decimal values input into the function, e.g. a month of 12.75 will be interpreted as 12.
NOW
Returns the current date and time as a date value.
Syntax
NOW()
Sample Usage
COALESCE(ANS(), NOW())
- Keeps the creation moment regardless of how many times the value gets recomputed.
Notes
- The output is an integer value representing the number of milliseconds since the Unix Epoch (Jan 1 1970 12AM UTC).
- The value of NOW() changes every time that it gets recomputed. Therefore it is useful to combine it with another function, such as COALESCE. See example above.
TODAY
Returns the current date as a date value.
Syntax
TODAY()
Sample Usage
COALESCE(ANS(), TODAY())
- Keeps the creation date regardless of how many times the value gets recomputed.
Notes
- The output is an integer value representing the number of milliseconds since the Unix Epoch (Jan 1 1970 12AM UTC).
- The value of TODAY() changes every time that it gets recomputed on a different day. Therefore it is useful to combine it with another function, such as COALESCE. See example above.
SODATE
Returns the start of the time unit (year, month, etc.) relative to the date value
Syntax
SODATE(date, "[unit]")
- date - value coming from a date field
- unit - a time period that will be set to the start. Available values:
- "year" | "month" | "week" | "day" | "hour" | "minute"
Sample Usage
SODATE(NOW(), "week") -> start of the current week (start of Monday)
SODATE(TODAY(), "week") -> start of the current week (start of Monday)
SODATE(TODAY(), "day") -> start of the current day
SODATE(TODAY(), "month") -> start of the current month
ADDDATE
Returns the modified date value by adding/subtracting time units.
Syntax
ADDDATE(date, [value], "[unit]")
- date - value coming from a date field
- value - count adding time, positive or negative integer
- unit - what needs to be added. Available value:
- "year" | "month" | "week" | "day" | "hour" | "minute"
Sample Usage
ADDDATE(NOW(), 1, 'week') -> add one week from the current time
ADDDATE(NOW(), -1, 'day') -> subtract 1 day from current time
ADDDATE(TODAY(), 4, 'month') -> add 4 months from start today time
Returns a date representing the first day of a week.
Syntax
SOWEEK(date, [weeks])
- date: date value coming from a date field
- weeks - [OPTIONAL]: The number of weeks before (negative) or after (positive) date to consider. The last calendar day of the calculated week is returned.
Sample Usage
SOWEEK(NOW()) -> start of the current week
SOWEEK(TODAY()) -> start of the current week
SOWEEK(TODAY(), 1) -> start of the next week
SOWEEK(TODAY(), -1) -> start of the previous week
Notes
- The output is an integer value representing the number of milliseconds since the Unix Epoch (Jan 1 1970 12AM UTC) start of week.
EOWEEK
Returns a date representing the last day of a week.
Syntax
EOWEEK(date, [weeks])
- date: date value coming from a date field
- weeks - [OPTIONAL]: The number of weeks before (negative) or after (positive) date to consider. The last calendar day of the calculated week is returned.
Sample Usage
EOWEEK(NOW()) -> end of the current week
EOWEEK(TODAY()) -> end of the current week
EOWEEK(TODAY(), 1) -> end of the next week
EOWEEK(TODAY(), -1) -> end of the previous week
Notes
- The output is an integer value representing the number of milliseconds since the Unix Epoch (Jan 1 1970 12AM UTC) end of week.
SOMONTH
Returns a date representing the first day of a month.
Syntax
SOMONTH(date, [months])
- date: date value coming from a date field
- months - [OPTIONAL]: The number of months before (negative) or after (positive) date to consider. The last calendar day of the calculated month is returned.
Sample Usage
SOMONTH(NOW()) -> start of the current month
SOMONTH(TODAY()) -> start of the current month
SOMONTH(TODAY(), 1) -> start of the next month
SOMONTH(TODAY(), -1) -> start of the previous month
Notes
- The output is an integer value representing the number of milliseconds since the Unix Epoch (Jan 1 1970 12AM UTC) start of month.
EOMONTH
Returns a date representing the last day of a month.
Syntax
EOMONTH(date, [months])
- date: date value coming from a date field
- months - [OPTIONAL]: The number of months before (negative) or after (positive) date to consider. The last calendar day of the calculated month is returned.
Sample Usage
EOMONTH(NOW()) -> end of the current month
EOMONTH(TODAY()) -> end of the current month
EOMONTH(TODAY(), 1) -> end of the next month
EOMONTH(TODAY(), -1) -> end of the previous month
Notes
- The output is an integer value representing the number of milliseconds since the Unix Epoch (Jan 1 1970 12AM UTC) end of month.
YEAR
Returns the year specified by a given date.
Syntax
YEAR(date)
- date: date value coming from a date field
Sample Usage
YEAR(NOW())
YEAR(TODAY())
Notes
- The output is an integer value representing the number of the year
MONTH
Returns the month of the year a specific date falls in.
Syntax
MONTH(date)
- date: date value coming from a date field
Sample Usage
MONTH(NOW())
MONTH(TODAY())
Notes
- The output is an integer value representing the number month of the year
DAY
Returns the day of the month that a specific date falls on.
Syntax
DAY(date)
- date: date value coming from a date field
Sample Usage
DAY(NOW())
DAY(TODAY())
Notes
- The output is an integer value representing the day of the month
TZ
Returns a date converted to the selected timezone.
Syntax
TZ(date, timezone)
- date: date value coming from a date field
- timezone: one of the timezones available in the database: List of available timezones
Sample Usage
For a date 25/07/2019 10:00:00:
TEXT(TZ(date, "Europe/Brussels"), "HH:mm") => 11:00
TEXT(TZ(date, "Europe/Lisbon"), "HH:mm") => 10:00
Notes
- If you want to display the date you need to pass the result of TZ to a TEXT function