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 Builder Example

Given the following table:

DATE
1994-10-03
1996-12-13

Applied formula:

ADD_MONTHS(O_ORDERDATE, 2)

Output:

DATE_ADD_MONTHS
1994-12-03
1997-02-13

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 Builder Example

Given an empty table:

TABLE

Applied formula:

DATE_FROM_PARTS(2021, 01, 17)

Output:

TABLE_DATE_FROM_PARTS
2021-01-17

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 Builder Example

Given the following table:

DATE
1994-10-03

Applied formula:

DAY( DATE )

Output:

DATE_DAY
3.0

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 Builder Example

Given the following table:

DATE
1995-10-13

Applied formula:

DAYOFWEEK(DATE)

Output:

DATE_DAYOFWEEK
5.0

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 Builder Example

Given the following table:

DATE
1995-10-13

Applied formula:

DAYOFMONTH(DATE)

Output:

DATE_DAYOFMONTH
13.0

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 Builder Example

Given the following table:

DATE
1992-12-17

Applied formula:

DAYOFYEAR(DATE)

Output:

DATE_DAYOFYEAR
352.0

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 Builder Example

Given the following table:

DATE
1992-12-17

Applied formula:

EXTRACT(YEAR FROM DATE)

Output:

DATE_EXTRACT
1992.0

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 Builder Example

Given the following table:

DateAndTime
2016-11-20 21:52:29

Applied formula:

HOUR(DateAndTime)

Output:

DateAndTime_2
21

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 Builder Example

Given the following table:

DateAndTime
2016-11-20 21:52:29

Applied formula:

MINUTE(DateAndTime)

Output:

DateAndTime_2
40

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 Builder Example

Given the following table:

DateAndTime
2016-11-20 21:52:29

Applied formula:

MONTH(DateAndTime)

Output:

DateAndTime_2
11

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 Builder Example

Given the following table:

DateAndTime
2016-11-20 21:52:29

Applied formula:

MONTHNAME(DateandTime)

Output:

DateAndTime_2
Nov

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 Builder Example

Given the following table:

DateAndTime_1 DateAndTime_2
2016-11-21 00:40:36 2017-02-21 00:40:36

Applied formula:

MONTHS_BETWEEN("DateAndTime_2","DateAndTime_1")

Output:

DateAndTime_delta
3.0

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 Builder Example

Given the following table:

DateAndTime_1
2016-11-20 22:21:41

Applied formula:

NEXT_DAY(DateAndTime_1,'su')

Output:

DateAndTime_2
2016-11-27

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 Builder Example

Given the following table:

DateAndTime_1
2016-11-20 23:51:32

Applied formula:

PREVIOUS_DAY(DateAndTime_1,'su')

Output:

DateAndTime_2
2016-11-13

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 Builder Example

Given the following table:

DateAndTime_1
2016-11-21 02:31:28

Applied formula:

QUARTER(DateAndTime_1)

Output:

DateAndTime_2
4

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 Builder Example

Given the following table:

DateAndTime_1
2016-11-21 05:30:33

Applied formula:

QUARTER(DateAndTime_1)

Output:

DateAndTime_2
33

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 Builder Example

Given the following table:

DateAndTime_1
2016-11-20 23:41:12

Applied formula:

TIME_FROM_PARTS(10, 11, 12)

Output:

DateAndTime_2
2022-02-23 10:11:12

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 Builder Example

Given the following table:

DateAndTime_1
2016-11-20 21:24:42
NULL
2016-05-17

Applied formula:

TO_DATE(DateAndTime_1)

Output:

DateAndTime_2
2016-11-20
NULL
2016-05-17

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 Builder Example

Given the following table:

DateAndTime_1
2016-11-20 21:24:42

Applied formula:

WEEK(DateAndTime_1)

Output:

DateAndTime_2
46

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 Builder Example

Given the following table:

DateAndTime_1
2016-11-20 23:51:32

Applied formula:

YEAR(DateAndTime_1)

Output:

DateAndTime_2
2016

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