I am a new learner in Netezza. Right now, I wanna write a sql statement to feedback the recent data objects corresponding to a defined recent date. Like return the data objects after 2014-06-10. Or return the objects before a certain date. Like return the data objects before 2014-03-10. How to do that? The attribute in where clause is timestamp. I assume it is a simple process; however, I cannot find it. Thank you in advance, guys!
Answer
You can compare the date or timestamp field to a date created with an explicit call to the to_date function, or you can simply compare it to a character literal of the date in a format that the compiler will recognize and implicitly cast to a date. For example:
TESTDB.ADMIN(ADMIN)=> select * from date_test;
COL1
---------------------
2014-06-23 00:00:00
2014-06-22 00:00:00
2014-06-24 20:44:51
(3 rows)
TESTDB.ADMIN(ADMIN)=> select * from date_test where col1 < '2014-06-23';
COL1
---------------------
2014-06-22 00:00:00
(1 row)
TESTDB.ADMIN(ADMIN)=> select * from date_test where col1 < to_date('2014-06-23', 'YYYY-MM-DD');
COL1
---------------------
2014-06-22 00:00:00
(1 row)