searching date into records

by mistake , i found some records having date formated like this
'02/Jan/00 3:21:36 AM'
how i can select records in this range of date to update it to the correct date value ?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

you mean you have text that looks like a date?

or you have a date and you don't like to view it like that?
slightwv (䄆 Netminder) Commented:
to_date('02/Jan/00 3:21:36 AM','DD/Mon/YY HH:MI:SS AM')

How to find them should be a simple regexp:
select column from table where regexp_like(column,'[0-9]{2}/[A-Za-z]{3}/[0-9]{2}');

This should look  at the first 9 characters.

It won't get 'good' dates since it will match: 99/ZZZ/99 but should match the general pattern.
if it's actually a date then

select * from your_table where
your_date_column =to_date('02/Jan/00 3:21:36 AM','dd/Mon/YY hh:mi:ss AM')
your_date_column =to_date('02/Jan/00 3:21:36 AM','dd/Mon/RR hh:mi:ss AM')

the two different formats are because we don't know what the 00 means,  it could be literally the year 0, or 1900 or 2000 or anything else ending in 00

if your data is actually text then

select * from your_table where
your_date_column = '02/Jan/00 3:21:36 AM'

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

the queries above are for a single value

what " range of date"  do you mean?  you only gave one date so no range.
awking00Information Technology SpecialistCommented:
If your date field is truly a date datatype, to_char(yourdatefield,'yyyy') will tell you if it's the year 0000,. the year 1900, or the year 2000. Once you know that the update will be easy.
NiceMan331Author Commented:
Actually I was appending dbf file into oracle , both dbf file and oracle table having same fileds names , but Ora table having additional tr_ date , date format which not available in the dbf , while appending using toad , then in section column mapping , I select expression to add date value , I select Constance thinking it will ask me the value of the date , but it didn't , I select some records I found the date becomes like this and want to correct them
NiceMan331Author Commented:
when i used awking code
it result 1900
then adjust sdstubar to be
select * from your_table where
your_date_column =to_date('02/Jan/1900 3:21:36 AM','dd/Mon/YyyY hh:mi:ss AM')
but for slighwv , it return no record , i don;'t know if i apply it correctly or not
awking00Information Technology SpecialistCommented:
>>when i used awking code
 it result 1900<<
Then the update should just be-
update yourtable
set yourdatefield = add_months(yourdatefield,1200)
where to_char(yourdatefield,'yyyy') = '1900';
NiceMan331Author Commented:
and how to remove time part from those texts
awking00Information Technology SpecialistCommented:
set yourdatefield = add_months(trunc(yourdatefield),1200)

Note - a date datatype will always have a time portion, even if it's 12:00:00 AM
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.