OraFAQ Forum: SQL & PL/SQL » to_char in a where clause

Home » SQL & PL/SQL » SQL & PL/SQL » to_char in a where clause

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

to_char in a where clause [message #1644]

Fri, 17 May 2002 07:02

David
Messages: 110
Registered: November 1998

Senior Member

Hello, i am trying to select data from a table which has a row called quotedate. this is a date datatype with values such as 01-may-02 (dd-mon-yy). Since I am working in java, and my date format is 01-05-02 (dd-mm-yy), i am trying to do

SELECT bytes FROM iq3_bkend_log WHERE userid=98 AND to_char(quotedate,'DD-MM-YYYY')='17-4-2002'

unfortunately i am not getting any rows at all- how can i do this?

Report message to a moderator

Re: to_char in a where clause [message #1645 is a reply to message #1644]

Fri, 17 May 2002 07:25

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

use something like this, using to_date function.

1* select * from emp where to_DATE(hiredate,'dd-mm-yyyy')=TO_DATE('17-DEC-80','DD-MM-YYYY')
SQL> /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20

Report message to a moderator

Re: to_char in a where clause [message #1647 is a reply to message #1644]

Fri, 17 May 2002 07:33

Dave
Messages: 92
Registered: August 1999

Member

well heres my code

st1 = con.createStatement();
sSQL=" SELECT bytes ";
sSQL+=" FROM iq3_bkend_log ";
sSQL+= " WHERE userid="+uid;
sSQL+=" AND to_char(quotedate,'DD-MM-YYYY')='"+today+"'";out.println(sSQL);
rs=st.executeQuery(sSQL);

the variable today is a String which is in the format dd-mm-yyyy but the date in the database in in format dd-mon-yy. i cannot do TO_DATE('17-DEC-80','DD-MM-YYYY') because instead of DEC it would be 12

Report message to a moderator

Previous Topic:	date in table
Next Topic:	Update trigger

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 10 15:08:26 CDT 2024