--- title: Date slug: date docTags: createdAt: 2023-09-12T08:23:18.964Z --- ### 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