All Forums Database
sakthikrr 53 posts Joined 07/12
30 Aug 2015
cast varchar value YYYY-MMM-DD HH:MI:SS to date

I have a VARCHAR column (CALL_START_DATE_TIME) and it's values are as shown below.

 

2014-OCT-31 15:52:35

2014-JUN-25 13:53:30

2013-DEC-26 19:56:40

2014-AUG-08 22:43:51

2015-APR-22 03:57:04

 

I need to apply date wise filter on this column as below:

 

sel count(*) from CALL_HISTORY_DETAILS where CALL_START_DATE_TIME = date '2015-08-30';

 

But I'm not sure how to cast the above values to date format. Please suggest.

Sakthi
Rohan_Sawant 55 posts Joined 07/14
30 Aug 2015

Hi Sakthikrr,
 
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,
Rohan Sawant

Adeel Chaudhry 773 posts Joined 04/08
30 Aug 2015

Yes, Rohan is correct. Though TIMESTAMP(0) will work fine as well.
 
HTH!

-- If you are stuck at something .... consider it an opportunity to think anew.

sakthikrr 53 posts Joined 07/12
30 Aug 2015

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.

Sakthi

dnoeth 4628 posts Joined 11/04
31 Aug 2015

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'

 

Dieter

sakthikrr 53 posts Joined 07/12
31 Aug 2015

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.

Sakthi

You must sign in to leave a comment.