Date/Time functions

DATEADD

Syntax:DATEADD( datetime [ , unit [ , number ] ] )

Returns the date obtained by adding unit in the amount of number to the specified date datetime.

The number argument is an integer. It can be negative. The unit argument takes the following values:

  • "year";
  • "month";
  • "day";
  • "hour";
  • "minute";
  • "second".

DATEPART

Syntax:DATEPART( datetime, unit [ , firstday ] )

Returns a part of the date as an integer.

Depending on the argument, unit returns the following values:

  • "year" — the year number (see YEAR);
  • "quarter" — the number of the quarter (from 1 to 4) of the year (see QUARTER);
  • "month" — the number of the month in the year (see MONTH);
  • "week" — the number of the week in the year according to ISO 8601 (see WEEK);
  • "dayofweek", "dow" — the number of the day of the week (see DAYOFWEEK);
  • "day" — the number of the day in the month (see DAY);
  • "hour" — the number of the hour in the day (see HOUR);
  • "minute" — the number of the minute in the hour (see MINUTE);
  • "second" — the number of the second in the minute (see SECOND).

If you select "dayofweek", you can use the additional parameter firstday to specify the first day of the week (Monday by default). Learn more about this parameter in the DAYOFWEEK function description.

DATETRUNC

Syntax:DATETRUNC( datetime, unit [ , number ] )

Rounds datetime down to the given unit. If optional number is given, then the value is rounded down to a number multiple of unit (omitting number is the same as number = 1).

Supported units:

  • "second";
  • "minute";
  • "hour";
  • "day" (acts as the day of the year if number is specified);
  • "week";
  • "month";
  • "quarter";
  • "year".

DAY

Syntax:DAY( datetime )

Returns the number of the day in the month of the specified date datetime.

DAYOFWEEK

Syntax:DAYOFWEEK( datetime [ , firstday ] )

Returns the day of the week according to ISO 8601.

  • Monday — 1.
  • Sunday — 7.

If the additional parameter firstday is specified, this day is considered the first day of the week. Valid values:

  • "Monday", "Mon" — Monday;
  • "Tuesday". "Tue" — Tuesday;
  • "Wednesday", "Wed" — Wednesday;
  • "Thursday", "Thu" — Thursday;
  • "Friday", "Fri" — Friday;
  • "Saturday", "Sat" — Saturday;
  • "Sunday", "Sun" — Sunday.

HOUR

Syntax:HOUR( datetime )

Returns the number of the hour in the day of the specified date and time datetime. When the date is specified without time, it returns 0.

MINUTE

Syntax:MINUTE( datetime )

Returns the number of the minute in the hour of the specified date datetime. When the date is specified without time, it returns 0.

MONTH

Syntax:MONTH( datetime )

Returns the number of the month in the year of the specified date datetime.

NOW

Syntax:NOW()

Returns the current date and time, depending on the data source and connection type.

QUARTER

Syntax:QUARTER( datetime )

Returns the number of the quarter (from 1 to 4) of the year of the specified date datetime.

SECOND

Syntax:SECOND( datetime )

Returns the number of the second in the minute of the specified date datetime. When the date is specified without time, it returns 0.

TODAY

Syntax:TODAY()

Returns the current date, depending on the data source and connection type.

WEEK

Syntax:WEEK( value )

The number of the week according to ISO 8601. The first week is the week that contains the first Thursday of the year or January 4th.

YEAR

Syntax:YEAR( datetime )

Returns the year number in the specified date datetime.

Previous
UNNEST
Next
DATEADD