When using TO_DATE function in Netezza, please make a note that TO_DATE function does not return (or rather consider) the TIME portion of it. So, if your queries that are normally written in Oracle which filters the data for certain Date and Time will result in wrong set in Netezza database.
Use TO_TIMESTAMP instead to consider the TIME portion also.
For Ex:
Query in Oracle:
select to_date('20120106143029','yyyymmddhh24miss'), to_timestamp('20120106143029','yyyymmddhh24miss') from dual;
TO_DATE TO_TIMESTAMP
1/6/2012 2:30:29 PM 1/6/2012 2:30:29.000000000 PM
Query in Netezza:
select to_date('20120106143029','yyyymmddhh24miss'), to_timestamp('20120106143029','yyyymmddhh24miss') from _v_dual;
Use TO_TIMESTAMP instead to consider the TIME portion also.
For Ex:
Query in Oracle:
select to_date('20120106143029','yyyymmddhh24miss'), to_timestamp('20120106143029','yyyymmddhh24miss') from dual;
TO_DATE TO_TIMESTAMP
1/6/2012 2:30:29 PM 1/6/2012 2:30:29.000000000 PM
Query in Netezza:
select to_date('20120106143029','yyyymmddhh24miss'), to_timestamp('20120106143029','yyyymmddhh24miss') from _v_dual;
TO_DATE TO_TIMESTAMP
1/6/2012 1/6/2012 2:30:29 PM
No comments:
Post a Comment