Reputation: 124
We have a situation, where we have a table (say Forms) in Oracle DB which has a column (say edition_date) of type date. It was strictly meant to hold the date information in YYYY-MM-DD (ex: 2012-11-23)format with no timestamp value.
Unfortunately, due to a code problem, lot of rows got created with timestamp values. There are tons of records in this table, and I want to update only those rows which had this bad data. I can do that using the query
UPDATE forms SET edition_date = TRUNC( edition_date )
I want to add a where clause to it, so it updates only the bad data. The problem, I am not sure how to retrieve those rows that has timestamp added to it. Below is a snapshot of the data I have:
FORM_ID EDITION_DATE
5 2012-11-23
6 2012-11-23 11:00:15
..
11 2010-07-11 15:23:22
..
13 2011-12-31
I want to retrieve only the row with form ids 6 and 11. I trioed using the length functions but I think that is good for Strings only. Is there any way to do this. Thanks anyone who can help me.
Upvotes: 2
Views: 582
Reputation: 52923
A date has no format; you're only seeing how it's displayed. However, the answer to your question is, effectively, what you've said:
I want to add a where clause to it, so it updates only the bad data.
So, where the date is not equal to the date without time:
update forms
set edition_date = trunc(edition_date)
where edition_date <> trunc(edition_date)
To ensure that this doesn't happen again you can add a check constraint to your table:
alter table forms
add constraint chk_forms_edition_date
check ( edition_date = trunc(edition_date) );
I would advise against all of the above. Don't destroy potentially useful data. You should simply select trunc(edition_date)
where you do not want time. You may want to use the time in the future.
You're correct, do not use LENGTH() etc for dates, it depends on your NLS_DATE_FORMAT settings and so could be different on a session-by-session basis. Always use date functions when dealing with dates.
Upvotes: 5