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 soweek 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