The follow query will do the required:
SEL COUNT(*) FROM CALL_HISTORY_DETAILS WHERE CAST(CALL_START_DATE_TIME AS TIMESTAMP(6) FORMAT 'YYYY-MMM-DDBHH:MI:SS') = DATE '2015-04-22';
Thanks for the reply Rohan and Adeel!
Still I'm getting Invalid Timestamp error. Even I tried the below code as well.
SEL COUNT(*) FROM CALL_HISTORY_DETAILS
WHERE cast(CAST(CALL_START_DATE_TIME AS TIMESTAMP(6) FORMAT 'YYYY-MMM-DDBHH:MI:SS') as date) = DATE '2015-04-22';
Is junk values on this column can give this error? Please suggest on how to handle them.
Any invalid data will result in "Invalid Timestamp".
Why is this column defined as a VarChar if it's supposed to be a Timestamp? This should be casted during load.
Instead of trying to cast too a timestamp you might simply use a character literal:
WHERE substring(CALL_START_DATE_TIME from 1 for 11) = '2015-APR-22'
Thanks Dieter! Very simple solution. Actually this column is defined as a VARCHAR in EDW table and I supposed to validate this data against BO report. I'm not sure about the purpose of VARCHAR here. Let me connect with my ETL designer on this.