Netezza Advanced Date Functions and Examples

  • Post author:
  • Post last modified:February 28, 2018
  • Post category:Netezza
  • Reading time:6 mins read

Netezza Advanced Date Functions are available in Netezza SQL extensions tool kit. You have to download it from IBM fix central and install it in required database. You cannot access these functions otherwise.

Read:

netezza advanced date functions

These advanced functions are different than standard Netezza data time functions. You can read about the standard Netezza date functions from below post:

You may want to read:

Netezza Advanced Date Functions List

Date Function Name Description
day() Returns the numeric value of the day of the month of a

specified date.
days_between() Determines the truncated number of full days

between two time stamps.
hour() Returns the hours value in a specified time.
hours_between() Returns the truncated number of full hours

between two time stamps.
minute() Returns the minutes value in a specified time.
minutes_between() Returns the truncated number of full minutes

between two time stamps.
month() Returns the month in a specified date.
next_week() Determines the first day of the next week after a

specified date. Sunday is considered the first day of that new week.
next_month() Determines the first day of the next month after a

specified date.
next_quarter() Determines the first day of the next quarter after a

specified date.
next_year() Returns the first day of the next year after a specified

date.
second() Returns the seconds value in a specified time
seconds_between() Determines the truncated number of full seconds

between two time stamps.
this_month() Determines the first day of the month of a specified

date.
this_quarter() Determines the first day of the quarter of a specified

date.
this_week() Determines the first day of the week in a specified date.
this_year() Returns the first day of the year of a specified date.
weeks_between() Determines the truncated number of full weeks

between two time stamps.
year() Determines the year of a specified date.

Netezza Advanced Date Functions Examples

Netezza day() Function Example

TRAINING.ADMIN(ADMIN)=> select SYSTEM..second(now()); 
 SECOND 
-------- 
 56 
(1 row)

Netezza days_between() Function Example

TRAINING.ADMIN(ADMIN)=> select SYSTEM..days_between('2016-02-27 06:12:33' , '2016-03-01 07:12:33'); 
 DAYS_BETWEEN 
-------------- 
 3 
(1 row)

Netezza hour() Function Example

TRAINING.ADMIN(ADMIN)=> select SYSTEM..hour (now()); 
 HOUR 
------ 
 20 
(1 row)

Netezza hours_between() Function Example

TRAINING.ADMIN(ADMIN)=> select SYSTEM..hours_between('2016-02-27 06:12:33' , '2016-03-01 07:12:33'); 
 HOURS_BETWEEN 
--------------- 
 73 
(1 row)

Netezza minute() Function Example

TRAINING.ADMIN(ADMIN)=> select SYSTEM..minute ('01:12:55'); 
 MINUTE 
-------- 
 12 
(1 row)

Netezza minutes_between() Function Example

TRAINING.ADMIN(ADMIN)=> select SYSTEM..minutes_between('1996-02-27 06:12:33' , '1996-02-27 07:12:00'); 
 MINUTES_BETWEEN 
----------------- 
 59 

(1 row)

Netezza month() Function Example

TRAINING.ADMIN(ADMIN)=> select SYSTEM..month('2016-2-29'); 
 MONTH 
------- 
 2 
(1 row)

Netezza next_week() Function Example

TRAINING.ADMIN(ADMIN)=> select SYSTEM..next_week('2009-06-28'); 
 NEXT_WEEK 
------------ 
 2009-07-05 
(1 row)

Netezza next_quarter() Function Example

TRAINING.ADMIN(ADMIN)=> select SYSTEM..next_quarter('1996-2-29'); 
 NEXT_QUARTER 
-------------- 
 1996-04-01 
(1 row)

Netezza next_year() Function Example

TRAINING.ADMIN(ADMIN)=> select SYSTEM..next_year('2016-2-29'); 
 NEXT_YEAR 
------------ 
 2017-01-01 
(1 row)

Netezza second() Function Example

TRAINING.ADMIN(ADMIN)=> select SYSTEM..second('01:12:55'); 
 SECOND 
-------- 
 55 
(1 row)

Netezza seconds_between() Function Example

TRAINING.ADMIN(ADMIN)=> select SYSTEM..seconds_between('1996-02-27 06:12:33','1996-02-27 06:55:22'); 
 SECONDS_BETWEEN 
----------------- 
 2569 
(1 row)

Netezza this_month() Function Example

TRAINING.ADMIN(ADMIN)=> select SYSTEM..this_month('1996-2-29'); 
 THIS_MONTH 
------------ 
 1996-02-01 
(1 row)

Netezza this_quarter() Function Example

TRAINING.ADMIN(ADMIN)=> select SYSTEM..this_quarter('2016-2-29'); 
 THIS_QUARTER 
-------------- 
 2016-01-01 
(1 row)

Netezza this_week() Function Example

TRAINING.ADMIN(ADMIN)=> select SYSTEM..this_week('2016-2-29'); 
 THIS_WEEK 
------------ 
 2016-02-28 
(1 row)

Netezza this_year() Function Example

TRAINING.ADMIN(ADMIN)=> select SYSTEM..this_year('2016-2-29'); 
 THIS_YEAR 
------------ 
 2016-01-01 
(1 row)

Netezza weeks_between() Function Example

TRAINING.ADMIN(ADMIN)=> select SYSTEM..weeks_between('1996-02-27 06:12:33' , '1996-03-05 07:12:33'); 
 WEEKS_BETWEEN 
--------------- 
 1 
(1 row)

Netezza year() Function Example

TRAINING.ADMIN(ADMIN)=> select SYSTEM..year('2016-2-29'); 
 YEAR 
------ 
 2016 
(1 row)
Tags: , ,