how to convert date "11/11/2009 6:08:29 AM " like this

[saideepak89]

hi i have the table in which date valve is like this 11/11/2009 6:08:29 AM how can i compare the value or select them , the to_date is working in SELECT but,i want to select the records using this colmn and by giving value
ie:

SELECT * FROM SENTMAIL WHERE SENTMAIL_SENT_DATE='01-OCT-2011'

this statement is retrieving nothing.

thanks in advance

[debasisdas]

try this.

SELECT * FROM SENTMAIL WHERE trunc(SENTMAIL_SENT_DATE)='01-OCT-2011'

[ruhi]

You can also use

SELECT * FROM SENTMAIL WHERE to_char(SENTMAIL_SENT_DATE, 'fmDD-MON-YYYY')='01-OCT-2011'

[saideepak89]

thanks the query is working ,but it is taking so much time

[debasisdas]

What is the volume of data ?