CODEWITHSUNDEEP
oracle-databasetimestamp
Chris
Chris

Reputation: 124

Oracle - retrieve date having timestamp values

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

Answers (1)

Ben
Ben

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

Related Questions