Date functions

This cheat sheet provides a quick reference guide for various date functions commonly used in data analysis and programming. Each function is accompanied by its syntax, a sample usage, and a brief description.

DATETIME_DIFF

The DATETIME_DIFF function calculates the difference between two dates in various units.

Syntax

DATETIME_DIFF(date1, date2, ["milliseconds" | "ms" | "seconds" | "s" | "minutes" | "m" | "hours" | "h" | "days" | "d" | "weeks" | "w" | "months" | "M" | "quarters" | "Q" | "years" | "y"])

Sample

DATETIME_DIFF("2022/10/14", "2022/10/15", "seconds") => -86400

Remark

This function compares two dates and returns the difference in the specified unit. Positive integers indicate that the second date is in the past compared to the first, and vice versa for negative values.

DATEADD

The DATEADD function adds a specified value to a date or datetime.

Syntax

DATEADD(date | datetime, value, ["second" | "minute" | "hour" | "day" | "week" | "month" | "year"])

Sample

DATEADD('2022-03-14 10:00:00', 30, 'second')  => 2022-03-14 10:00:30
DATEADD('2022-03-14 10:00:00', 15, 'minute')  => 2022-03-14 10:15:00
DATEADD('2022-03-14 10:00:00', 2, 'hour')     => 2022-03-14 12:00:00
DATEADD('2022-03-14', 1, 'day')               => 2022-03-15
DATEADD('2022-03-14', 1, 'week')              => 2022-03-21
DATEADD('2022-03-14', 1, 'month')             => 2022-04-14
DATEADD('2022-03-14', 1, 'year')              => 2023-03-14

Conditional Example

IF(NOW() < DATEADD(date, 10, 'day'), "true", "false")
=> If the current date is less than the specified date plus 10 days, it returns true. Otherwise, it returns false.

Remark

This function supports date and datetime fields, can handle negative values, and works with units down to seconds.

NOW

The NOW function returns the current time and day.

Syntax

NOW()

Sample

NOW() => 2022-05-19 17:20:43 (current date & time)

Conditional Example

IF(NOW() < date, "true", "false") => If the current date is less than the specified date, it returns true. Otherwise, it returns false.

Remark

This function provides the current time and day, supporting datetime fields and negative values.

WEEKDAY

The WEEKDAY function returns the day of the week as an integer.

Syntax

WEEKDAY(date, [startDayOfWeek])

Sample

WEEKDAY(NOW()) => If today is Monday, it returns 0.
WEEKDAY(NOW(), "sunday") => If today is Monday, it returns 1.

Remark

Returns the day of the week as an integer between 0 and 6 (inclusive), with Monday as the default start day. The start day of the week can be optionally changed by specifying it as the second argument.

DATESTR

The DATESTR function converts a date or datetime field into a string in "YYYY-MM-DD" format.

Syntax

DATESTR(date | datetime)

Sample

DATESTR('2022-03-14') => 2022-03-14
DATESTR('2022-03-14 12:00:00') => 2022-03-14

Remark

This function converts a date or datetime field into a string in "YYYY-MM-DD" format, ignoring the time part.

DATETIME_FORMAT

The DATETIME_FORMAT function formats a date or datetime field into a string using a custom Day.js format, including localized presets such as LL, LLL and LLLL.

Syntax

DATETIME_FORMAT(date | datetime, ["format"])

Sample

DATETIME_FORMAT('2018-08-16 20:02')                 => 2018-08-16 20:02   (default format)
DATETIME_FORMAT('2018-08-16 20:02', "YYYY-MM-DD")   => 2018-08-16
DATETIME_FORMAT('2018-08-16 20:02', "DD MMM YYYY")  => 16 Aug 2018
DATETIME_FORMAT('2018-08-16 20:02', "hh:mm A")      => 08:02 PM
DATETIME_FORMAT('2018-08-16 20:02', "dddd")         => Thursday
DATETIME_FORMAT('2018-08-16 20:02', "LLL")          => August 16, 2018 8:02 PM
DATETIME_FORMAT('2018-08-16 20:02', "[Year:] YYYY") => Year: 2018

Common format tokens

These tokens behave consistently across the supported databases. For the full token list, see the Day.js format reference. Examples below use Thursday, August 16, 2018 8:02:18 PM.

Wrap literal text in square brackets so it is not interpreted as tokens, for example "[Year:] YYYY".

Localized presets

Presets expand to a locale-friendly format. Examples use the same datetime as above.

Remark

This function formats a date or datetime field into a string. When no format is provided, it defaults to YYYY-MM-DD HH:mm. The format argument must be a constant text value (a quoted string); it cannot be a field reference or expression.

DATETIME_FORMAT is available on PostgreSQL, MySQL/MariaDB and SQLite (including NocoDB Cloud, which runs on PostgreSQL). It is not available on SQL Server, Oracle, Snowflake or Databricks.

DAY

The DAY function returns the day of the month as an integer.

Syntax

DAY(date | datetime)

Sample

DAY('2022-03-14') => 14
DAY('2022-03-14 12:00:00') => 14

Remark

This function returns the day of the month as an integer between 1 and 31 (inclusive). Note that the day information retrieved is based on the timezone of the server (GMT by default). If the browser timezone is different from the server timezone, the day value may differ.

YEAR

The YEAR function returns the year as an integer.

Syntax

YEAR(date | datetime)

Sample

YEAR('2022-03-14') => 2022
YEAR('2022-03-14 12:00:00') => 2022

Remark

This function returns the year as an integer. The year information retrieved is based on the timezone of the server (GMT by default). If the browser timezone is different from the server timezone, the year value may differ.

MONTH

The MONTH function returns the month of the year as an integer.

Syntax

MONTH(date | datetime)

Sample

MONTH('2022-03-14') => 3
MONTH('2022-03-14 12:00:00') => 3

Remark

This function returns the month of the year as an integer between 1 and 12 (inclusive). Note that the month information retrieved is based on the timezone of the server (GMT by default). If the browser timezone is different from the server timezone, the month value may differ.

HOUR

The HOUR function returns the hour of the day as an integer.

Syntax

HOUR(datetime)

Sample

HOUR('2022-03-14 12:00:00') => 12

Remark

This function returns the hour of the day as an integer between 0 and 23 (inclusive). Hour information retrieved is based on a 24-hour clock & will be based on the timezone of the server (GMT by default). Note that, if browser timezone is different from the server timezone, the hour value may differ.