SQL Server Performance Forum – Threads Archive

PROBLEM WITH DATE TYPE DATA IN SQL QUERY

I TRY TO RETRIEVE DATA FROM AN SQL SERVER THROUGH AN ORACLE PROGRAM UNIT (I HAVE AN ODBC SQL SERVER CONNECTION) AND THE SQL STRING IGNORE THE WHERE STATEMENT THE QUERY IS: sqlstr := ‘ SELECT CONVERT(VARCHAR(10), date, 103)AS EXPR1 FROM tabledata ‘; sqlstr := sqlstr || ‘ WHERE EXPR1 = "30/12/2007"’; date column is of type datetime(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> DO YOU HAVE ANY SUGGESTIONS??? Katia[V]

In the WHERE statement, you can’t refer to an alias from the column list – you have to repeat the column name (or the expression). You also need to use single quotes around constant string expressions. I assume that in a constant expression in Oracle, like in T-SQL, you have to type two single quotes (not one double-quote character). sqlstr := ‘ SELECT CONVERT(VARCHAR(10), date, 103)AS EXPR1 FROM tabledata ‘;
sqlstr := sqlstr || ‘ WHERE CONVERT(VARCHAR(10), date, 103) = ”30/12/2007”’;

Adriaan, I tried your suggestion but unfortunately I still take an "unhandled error exception" message.[<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />] <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Adriaan In the WHERE statement, you can’t refer to an alias from the column list – you have to repeat the column name (or the expression). You also need to use single quotes around constant string expressions. I assume that in a constant expression in Oracle, like in T-SQL, you have to type two single quotes (not one double-quote character). sqlstr := ‘ SELECT CONVERT(VARCHAR(10), date, 103)AS EXPR1 FROM tabledata ‘; sqlstr := sqlstr || ‘ WHERE CONVERT(VARCHAR(10), date, 103) = ”30/12/2007”’; <hr height="1" noshade id="quote"></blockquote id="quote">

Perhaps best to wait for someone who has worked with the Oracle tools …

]]>