cast varchar value YYYY-MMM-DD HH:MI:SS to date

Database
Enthusiast

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.

5 REPLIES
Junior Supporter

Re: cast varchar value YYYY-MMM-DD HH:MI:SS to date

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

Teradata Employee

Re: cast varchar value YYYY-MMM-DD HH:MI:SS to date

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

HTH!

Enthusiast

Re: cast varchar value YYYY-MMM-DD HH:MI:SS to date

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.

Junior Contributor

Re: cast varchar value YYYY-MMM-DD HH:MI:SS to date

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'
Enthusiast

Re: cast varchar value YYYY-MMM-DD HH:MI:SS to date

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.