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<br />THE QUERY IS:<br />sqlstr := ‘ SELECT CONVERT(VARCHAR(10), date, 103)AS EXPR1 FROM tabledata ‘;<br />sqlstr := sqlstr || ‘ WHERE EXPR1 = "30/12/2007"’;<br /><br />date column is of type datetime(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /><br /><br />DO YOU HAVE ANY SUGGESTIONS???<br /><br />Katia[V]<br />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=’
![Frown :( :(](styles/default/xenforo/smilies/frown.png)
Perhaps best to wait for someone who has worked with the Oracle tools …
]]>