Archives of the TeradataForum

Message Posted: Wed, 22 Oct 2003 @ 11:25:58 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi,

I am looking for a conversion of character string to date using cast function. The only issue here is that the string is part of IN clause.

Following is the requirement -

SELECT * FROM tablex WHERE colx IN ( CAST( '2000/01/01' AS DATE FORMAT 'YYYY/DD/MM'), CAST( '2001/01/01' AS DATE FORMAT 'YYYY/DD/MM') );

WHERE col_x is of type varchar and the values '2000/01/01' are coming as input through an application. The IN clause can have more number of values.

The above query when submitted results in a syntax error. It could be possible that casting is not allowed in IN-clause.

The following solutions were taken up -

1. Implicit casting - Teradata manuals say that the character string can be converted to date internally if it is provided in a correct format.

select * from tablex where colx in ( '2000/01/01' , '2001/10/10');

But here there could be a risk if the string provided is not in correct format, then it would report error.

2. The following query work fine -

select * from tablex where colx in ( date '2005-10-10' , date '2001-10-10' )

but if it is submitted with '/' in string, it results in syntax error -

select * from tablex where colx in ( date '2005/10/10' , date '2001/10/10' )

But my requirement is similar to the second one.

3. The cast function works fine if IN clause is replaced by OR List. Only problem is that the OR list might get too big -

select * from tablex where colx = cast ('2005/10/10' as date format 'yyyy-dd-mm') or colx = cast ('2001/10/10' as date format 'yyyy-dd-mm')

select * from tablex where colx = cast ('2005-10-10' as date format 'yyyy-dd-mm') or colx = cast ('2001-10-10' as date format 'yyyy-dd-mm')

Is there any other good method to cast the character string to date in the IN clause.

Thanks,

Neeraj

	<Prev	Next>		<<First	<Prev	Next>	Last>>