Formatting Millisecond in Oracle Date

Had this weird problem today.. timestamp is stored into date field..and so there there is this extra 0 ... which is throwing an error if I am using regular oracle date formatting with to_date.

Sample dt stored in db: 1/5/2006 12:38:30.000 PM

expected date in to_date function is something like.. to_date('10/01/2007 00:00:00','MM/DD/YYYY HH24:MI:SS')

there is no 'SSS' which takes millisecond.

here is what I did. Process it first as to_char to eliminate the extra 0 in millisec and use it for ur date function.

to_date(to_char(PROPOSAL_CREATION_DATE,'MM/DD/YYYY HH24:MI:SS') ,'MM/DD/YYYY HH24:MI:SS')

here is a sample query to get all rows in a month

select * from table1 where to_date(to_char(CREATION_DATE,'MM/DD/YYYY HH24:MI:SS') ,'MM/DD/YYYY HH24:MI:SS') > to_date('10/01/2007 00:00:00','MM/DD/YYYY HH24:MI:SS')
and to_date(to_char(CREATION_DATE,'MM/DD/YYYY HH24:MI:SS') ,'MM/DD/YYYY HH24:MI:SS')<= to_date('11/01/2007 00:00:00','MM/DD/YYYY HH24:MI:SS')

it works perfectly well ..yay.. happy coding. :)