TO_CHAR query help?

[scottyjohn]

Hi all,
I am trying to get some training material on the whole TO_CHAR and TO_DATE functions of Oracle DBMS but in the mean time I need help with the following query?
I have a view which has a date field defined as

TO_CHAR(TO_DATE(STARTLOCALTIME,'DD/MM/RRRR HH24:MI:SS'),'DD/MM/RRRR') as STARTDATE

which displays the field values as say, 17/10/2006. I need to query this fields and was trying

SELECT * FROM V_STATUS WHERE STARTDATE ='17/10/2006' but this doesnt work. Can anyone tell me how I should define that field's value in my query?
Thanks in advance!

John

 

[SantaMufasa]

With the presumption that STARTDATE is, in fact, an expression of type DATE, you can say:

SELECT * FROM V_STATUS WHERE STARTDATE =[b]to_date([/b]'17/10/2006'[b],'dd/mm/yyyy')[/b]

Let us know how that works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[scottyjohn]

Hi SM,
Sorry for not getting back, we have found that there is a problem with the underlying data in that the application which writes the records has an option to define the format of the timestamp. Somewhere along the line our service provider changed this format and so we now have two different fomats in that table and this is why my queries were always failing on date.
Your query does now work, thank you very much for your help!

John