Date Functions

This cheat sheet is a quick reference for date functions that are frequently used in data analysis and programming. Each function has its syntax, a sample and a short description.

Ask NocoAI

Copy a prompt, paste it into NocoAI in any base, and replace the words in [brackets] with your own.

Add a formula field that shows the number of days between [Start Date] and [End Date]
Add a formula field that shows the weekday of [Order Date]
Add a formula field that adds [30] days to [Invoice Date]

DATETIME_DIFF

The DATETIME_DIFF function calculates the difference between two dates in different 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

DATETIME_DIFF compares two dates and returns the difference in the specified unit. A positive integer means that the second date is before the first date. A negative integer means that the second date is after the first date.

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

DATEADD supports date and datetime fields. It accepts negative values. The smallest unit is 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

NOW returns the current time and day. It supports 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

WEEKDAY returns the day of the week as an integer between 0 and 6 (inclusive). The default start day is Monday. To change the start day of the week, give it as the second argument (optional).

WEEKNUM

The WEEKNUM function returns the week of the year of a date, as an integer.

Syntax

WEEKNUM(date, [startDayOfWeek])

Sample

WEEKNUM("2021-01-01") => 1
WEEKNUM("2021-01-03") => 2
WEEKNUM("2021-06-09") => 24
WEEKNUM("2021-01-03", "monday") => 1

Remark

Week 1 is the week that contains January 1st. Thus the first week of the year is frequently shorter than seven days. By default, weeks start on Sunday. To change the start day, give a day name as the second argument, for example "monday". The accepted values are "sunday" through "saturday". The result is an integer between 1 and 54. Spreadsheets calculate week numbers in the same way.

By default, WEEKNUM starts weeks on Sunday, but WEEKDAY starts them on Monday. If you need the two functions to agree, give the second argument to both.

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

DATESTR converts a date or datetime field into a string in "YYYY-MM-DD" format. It ignores the time part.

DATETIME_FORMAT

The DATETIME_FORMAT function formats a date or datetime field into a string. It uses 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 DATETIME_FORMAT tokens work the same on all supported databases. For the full token list, refer to the Day.js format reference. The examples below use Thursday, August 16, 2018 8:02:18 PM.

Token	Output	Description
`YYYY`	2018	4-digit year
`YY`	18	2-digit year
`MMMM`	August	Full month name
`MMM`	Aug	Short month name
`MM`	08	Month, 2 digits
`M`	8	Month, no leading zero
`Do`	16th	Day of month with ordinal
`DD`	16	Day of month, 2 digits
`D`	16	Day of month, no leading zero
`dddd`	Thursday	Full weekday name
`ddd`	Thu	Short weekday name
`HH`	20	Hour, 24-hour, 2 digits
`H`	20	Hour, 24-hour, no leading zero
`hh`	08	Hour, 12-hour, 2 digits
`h`	8	Hour, 12-hour, no leading zero
`mm`	02	Minute, 2 digits
`m`	2	Minute, no leading zero
`ss`	18	Second, 2 digits
`s`	18	Second, no leading zero
`SSS`	000	Millisecond, 3 digits
`A`	PM	Uppercase AM/PM
`a`	pm	Lowercase am/pm

Put literal text in square brackets, so that the function does not read it as tokens. For example: "[Year:] YYYY".

Localized presets

Each preset expands to a format for the locale. The examples use the same datetime as above.

Preset	Expands to	Output
`LT`	h:mm A	8:02 PM
`LTS`	h:mm:ss A	8:02:18 PM
`L`	MM/DD/YYYY	08/16/2018
`LL`	MMMM D, YYYY	August 16, 2018
`LLL`	MMMM D, YYYY h:mm A	August 16, 2018 8:02 PM
`LLLL`	dddd, MMMM D, YYYY h:mm A	Thursday, August 16, 2018 8:02 PM
`l`	M/D/YYYY	8/16/2018
`ll`	MMM D, YYYY	Aug 16, 2018
`lll`	MMM D, YYYY h:mm A	Aug 16, 2018 8:02 PM
`llll`	ddd, MMM D, YYYY h:mm A	Thu, Aug 16, 2018 8:02 PM

Remark

DATETIME_FORMAT formats a date or datetime field into a string. If you do not give a format, the default is YYYY-MM-DD HH:mm. The format argument must be a constant text value (a quoted string). It cannot be a field reference or an 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.

On SQLite, the Do (ordinal day) token renders as a plain day number without the ordinal suffix (for example 16 instead of 16th).

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

DAY returns the day of the month as an integer between 1 and 31 (inclusive). The day comes from the timezone of the server (GMT by default). If the browser timezone is different from the server timezone, the day value can be different.

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

YEAR returns the year as an integer. The year comes from the timezone of the server (GMT by default). If the browser timezone is different from the server timezone, the year value can be different.

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

MONTH returns the month of the year as an integer between 1 and 12 (inclusive). The month comes from the timezone of the server (GMT by default). If the browser timezone is different from the server timezone, the month value can be different.

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

HOUR returns the hour of the day as an integer between 0 and 23 (inclusive). The hour uses a 24-hour clock and comes from the timezone of the server (GMT by default). If the browser timezone is different from the server timezone, the hour value can be different.