CODEWITHSUNDEEP
datetimenetezza
Victor
Victor

Reputation: 17097

Netezza date function not working properly

Why both these are returning 0?

select EXTRACT (DAY FROM date('08DEC2015') - current_timestamp) from _v_dual

select EXTRACT (DAY FROM date('09DEC2015') - current_timestamp) from _v_dual

Today is 8Dec2015,11:50 AM EST as I write this.

Upvotes: 0

Views: 221

Answers (1)

ScottMcG
ScottMcG

Reputation: 3887

The DATE function is working just fine. The EXTRACT of the DAY is returning zero, because in both cases the interval returned when you subtract the current_timestamp from the hardcoded date function is less than a day.

TESTDB.ADMIN(ADMIN)=> select date('08DEC2015') - current_timestamp;
 ?COLUMN?
-----------
 -15:30:12
(1 row)

TESTDB.ADMIN(ADMIN)=> select date('09DEC2015') - current_timestamp;
 ?COLUMN?
----------
 08:29:42
(1 row)

If you have an interval greater than 24 hours, then you will EXTRACT a non-zero DAY value from the expression. As an aside, note that "from _v_dual" is unnecessary for a SELECT in Netezza.

TESTDB.ADMIN(ADMIN)=> select date('10DEC2015') - current_timestamp;
    ?COLUMN?
----------------
 1 day 08:26:11
(1 row)

TESTDB.ADMIN(ADMIN)=> select EXTRACT (DAY FROM date('10DEC2015') - current_timestamp);
 DATE_PART
-----------
         1
(1 row)

Upvotes: 1

Related Questions