Skip to content

Date & Time Operations#

ADD_MONTHS#

Syntax

ADD_MONTHS(<date_or_time_expr>, <num_months_expr>)

Description

Adds or subtracts a specified number of months to a date or timestamp, preserving the end-of-month information. The data type of the returned value is the same as the data type of the first parameter. For example, if the input is a DATE, then the output is a DATE.

View examples

Formula Editor Example

SQL Editor Example

Add 2 months to a date and cast the date to a timestamp with no time zone:

select add_months('2016-05-15'::timestamp_ntz, 2) as result;
RESULT
2016-07-15 00:00:00.000

DATE_FROM_PARTS#

Syntax

DATE_FROM_PARTS( <year>, <month>, <day> )

Description

Creates a date from individual numeric components that represent the year, month, and day of the month.

View examples

Formula Editor Example

SQL Editor Example

select date_from_parts(1977, 8, 7);
DATE_FROM_PARTS(1977, 8, 7)
1977-08-07

DAY#

Syntax

DAY( <date_or_timestamp_expr> )

Description

Extracts the day from a date or timestamp.

View examples

Formula Editor Example

SQL Editor Example

select
    '2013-05-08T23:39:20.123-07:00'::timestamp as tstamp,
    day(tstamp) as "DAY";
TSTAMP DAY
2013-05-08 23:39:20.123 8

DAYOFWEEK#

Syntax

DAYOFWEEK( <date_or_timestamp_expr> )

Description

Extracts the day of the week from a date or timestamp.

View examples

Formula Editor Example

SQL Editor Example

select
    '2013-05-08T23:39:20.123-07:00'::timestamp as tstamp,
    dayofweek(tstamp) as "DAY OF WEEK";
TSTAMP DAY OF WEEK
2013-05-08 23:39:20.123 3

DAYOFMONTH#

Syntax

DAYOFMONTH( <date_or_timestamp_expr> )

Description

Extracts the day of the month from a date or timestamp.

View examples

Formula Editor Example

SQL Editor Example

select
    '2013-05-08T23:39:20.123-07:00'::timestamp as tstamp,
    dayofmonth(tstamp) as "DAY OF MONTH";
TSTAMP DAY OF MONTH
2013-05-08 23:39:20.123 8

DAYOFYEAR#

Syntax

DAYOFYEAR( <date_or_timestamp_expr> )

Description

Extracts the day of the year from a date or timestamp.

View examples

Formula Editor Example

SQL Editor Example

select
    '2013-05-08T23:39:20.123-07:00'::timestamp as tstamp,
    dayofyear(tstamp) as "DAY OF YEAR";
TSTAMP DAY OF YEAR
2013-05-08 23:39:20.123 128

EXTRACT#

Syntax

EXTRACT(<date_or_time_part> FROM <date_or_time_expr>)

Description

Extracts the specified date or time part from a date, time, or timestamp.

View examples

Formula Editor Example

SQL Editor Example

select extract(year from to_timestamp('2013-05-08T23:39:20.123-07:00')) as v
  from (values(1)) v1;
V
2013

HOUR#

Syntax

HOUR( <time_or_timestamp_expr> )

Description

Extracts the hour from a time or timestamp value. Possible values are 0 to 23.

View examples

Formula Editor Example

SQL Editor Example

select '2013-05-08T23:39:20.123-07:00'::timestamp as tstamp,
      hour(tstamp) as "HOUR";
TSTAMP HOUR
2013-05-08 23:39:20.123 23

MINUTE#

Syntax

MINUTE( <time_or_timestamp_expr> )

Description

Extracts the minute from a time or timestamp value.

View examples

Formula Editor Example

SQL Editor Example

select '2013-05-08T23:39:20.123-07:00'::timestamp as tstamp,
      minute(tstamp) as "MINUTE";
TSTAMP MINUTE
2013-05-08 23:39:20.123 39

MONTH#

Syntax

MONTH( <date_or_timestamp_expr> )

Description

Extracts the month from a date or timestamp.

View examples

Formula Editor Example

SQL Editor Example

select '2013-05-08T23:39:20.123-07:00'::timestamp as tstamp,
      second(tstamp) as "SECOND";
TSTAMP SECOND
2013-05-08 23:39:20.123 20

MONTHNAME#

Syntax

MONTHNAME(<date_or_time_expr>)

Description

Extracts the three-letter month name from the specified date or timestamp.

View examples

Formula Editor Example

SQL Editor Example

select monthname(to_date('2015-05-01')) as month;
MONTH
May

MONTHS_BETWEEN#

Syntax

MONTHS_BETWEEN( <date_expr1> , <date_expr2> )

Description

Returns the number of months between two DATE or TIMESTAMP values.

View examples

Formula Editor Example

SQL Editor Example

This example shows differences in whole months. The first pair of dates have the same day of the month (the 15th). The second pair of dates are both the last days in their respective months (February 28th and March 31st).

select
  months_between('2019-03-15'::date,
                '2019-02-15'::date) as monthsbetween1,
  months_between('2019-03-31'::date,
                '2019-02-28'::date) as monthsbetween2;

| MONTHSBETWEEN1 | MONTHSBETWEEN2 | |----------------+----------------| | 1.000000 | 1.000000 |

NEXT_DAY#

Syntax

NEXT_DAY( <date_or_time_expr> , <dow_string> )

Description

Returns the date of the first specified DOW (day of week) that occurs after the input date. The return value is always a date regardless of whether date_or_time_expr is a date or timestamp.

View examples

Formula Editor Example

SQL Editor Example

Return the date of the next Friday that occurs after the current date:

select current_date() as "Today's Date",
    next_day("Today's Date", 'Friday ') as "Next Friday";

| Today's Date | Next Friday | |--------------+-------------| | 2018-06-12 | 2018-06-15 |

PREVIOUS_DAY#

Syntax

PREVIOUS_DAY( <date_or_time_expr> , <dow_string> )

Description

Returns the date of the first specified DOW (day of week) that occurs before the input date. The return value is always a date regardless of whether date_or_time_expr is a date or timestamp.

View examples

Formula Editor Example

SQL Editor Example

Return the date of the previous Friday that occurred before the current date:

select current_date() as "Today's Date",
    previous_day("Today's Date", 'Friday ') as "Previous Friday";

| Today's Date | Previous Friday | |--------------+-----------------| | 2018-06-12 | 2018-06-08 |

QUARTER#

Syntax

QUARTER( <date_or_timestamp_expr> )

Description

Extracts the quarter from a date or timestamp.

View examples

Formula Editor Example

SQL Editor Example

select
    '2013-05-08T23:39:20.123-07:00'::timestamp as tstamp,
    quarter(tstamp) as "QUARTER OF YEAR";
TSTAMP QUARTER OF YEAR
2013-05-08 23:39:20.123 2

SECOND#

Syntax

SECOND( <time_or_timestamp_expr> )

Description

Extracts the second from a time or timestamp value.

View examples

Formula Editor Example

SQL Editor Example

select '2013-05-08T23:39:20.123-07:00'::timestamp as tstamp,
      second(tstamp) as "SECOND";
TSTAMP SECOND
2013-05-08 23:39:20.123 20

TIME_FROM_PARTS#

Syntax

TIME_FROM_PARTS( <hour>, <minute>, <second> [, <nanoseconds>] )

Description

Creates a time from individual numeric components. TIME_FROM_PARTS is typically used to handle values in “normal” ranges (e.g. hours 0-23, minutes 0-59), but it also handles values from outside these ranges. This allows, for example, choosing the N-th minute in a day, which can be used to simplify some computations.

View examples

Formula Editor Example

SQL Editor Example

alter session set time_output_format='HH24:MI:SS.FF9';

select time_from_parts(12, 34, 56, 987654321);
TIME_FROM_PARTS(12, 34, 56, 987654321)
12:34:56.987654321

TO_DATE#

Syntax

TO_DATE( <string_expr> [, <format> ] )

Description

Converts an input expression to a date. For a string expression, the result of converting the string to a date. For a timestamp expression, the date from the timestamp. For NULL input, the output is NULL. The data type of the returned value is DATE.

View examples

Formula Editor Example

SQL Editor Example

select to_date('2013-05-17'), date('2013-05-17');

| TO_DATE('2013-05-17') | DATE('2013-05-17') | |-----------------------+--------------------| | 2013-05-17 | 2013-05-17 |

WEEK#

Syntax

WEEK( <date_or_timestamp_expr> )

Description

Extracts the week from a date or timestamp.

View examples

Formula Editor Example

SQL Editor Example

alter session set week_of_year_policy = 1;

select
    '2016-01-02T23:39:20.123-07:00'::timestamp as tstamp,
    week(tstamp) as "WEEK";
TSTAMP WEEK
2016-01-02 23:39:20.123 1

YEAR#

Syntax

YEAR( <date_or_timestamp_expr> )

Description

Extracts the year from a date or timestamp.

View examples

Formula Editor Example

SQL Editor Example

select
    '2013-05-08T23:39:20.123-07:00'::timestamp as tstamp,
    year(tstamp) as "YEAR";
TSTAMP YEAR
2013-05-08 23:39:20.123 2013