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 ?
NiceMan331Asked:
Who is Participating?
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.

sdstuberCommented:
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?
0
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.
0
sdstuberCommented:
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')
or
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'
0

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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sdstuberCommented:
the queries above are for a single value

what " range of date"  do you mean?  you only gave one date so no range.
0
awking00Commented:
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.
0
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
0
NiceMan331Author Commented:
ok
when i used awking code
 to_char(yourdatefield,'yyyy')
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')
solved
but for slighwv , it return no record , i don;'t know if i apply it correctly or not
0
awking00Commented:
>>when i used awking code
  to_char(yourdatefield,'yyyy')
 it result 1900<<
Then the update should just be-
update yourtable
set yourdatefield = add_months(yourdatefield,1200)
where to_char(yourdatefield,'yyyy') = '1900';
0
NiceMan331Author Commented:
and how to remove time part from those texts
0
awking00Commented:
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
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.