CODEWITHSUNDEEP
datecomparisonnetezza
jawsnnn
jawsnnn

Reputation: 91

Date comparison in Netezza does not work properly

I am trying to run a simple query that excludes all records from the table which are greater than the maximum value of a date column. e.g.

SELECT * FROM TABLE1
WHERE LD_TMSTMP > (SELECT MAX(LD_TMSTMP) FROM TABLE1)
===========================================
0 records returned

This query should return zero records and it does that. However when I try to run the inner query I get this:

SELECT MAX(LD_TMSTMP) FROM TABLE1
===========================================
2015-04-22 06:42:32

And when I put this value in the same query I get 131 records

SELECT * FROM TABLE1
WHERE LD_TMSTMP > TO_DATE('2015-04-22 06:42:32','YYYY-MM-DD HH24:MI:SS')
===========================================
131 records returned

Does anyone know why this happens? Do I need to use a better precision value when returning the date in string format?

Upvotes: 0

Views: 1556

Answers (1)

ScottMcG
ScottMcG

Reputation: 3887

The problem here is that you are confusing DATE with TIMESTAMP. The tipoff is in your title, which references a DATE, but your data shows you are clearly dealing with a TIMESTAMP.

From your SQL we can see you are using TO_DATE rather than TO_TIMESTAMP to convert your character representation, which unsurprisingly gives us a DATE.

TESTDB.ADMIN(ADMIN)=> select TO_DATE('2015-04-22 06:42:32','YYYY-MM-DD HH24:MI:SS');
  TO_DATE
------------
 2015-04-22
(1 row)

TESTDB.ADMIN(ADMIN)=> select to_timestamp('2015-04-22 06:42:32','YYYY-MM-DD HH24:MI:SS');
    TO_TIMESTAMP
---------------------
 2015-04-22 06:42:32
(1 row)

Upvotes: 1

Related Questions