CODEWITHSUNDEEP
sqloracle-databasedateoracle10g
Mt Kr
Mt Kr

Reputation: 129

Convert a character column with multiple date formats to a date

I'm trying to update a date column with from a varchar column

update tbl set columnA = columnB

Here, columnA is a varchar data type and columnB is a date data type. columnA has various types of date formats, for instance, 09302012, 9/30/2012, 2012-09-30 and more different types

How can I write a single query to update the column with the various types of date formats in single query.

EDITED:::

sorry about the mess up.. i just Realized that these are individual(pieces) updates in ssis package...

we have diff types(6) of makes and for each make they send different files with different date formats

for type 1 contains date format like 09/22/2011 9/22/2011 and 2012-09-22

and the rest all types follows same format..its 09222012

so now i need to write query for individual types...(straightly speaking only two logics one for type 1 and the other for rest all types)

the first query logic contains a case statement for three formats and the second query logic contain logic for other format...

the end result should show up like 2012-09-22 00:00:00 (ie yyyy-dd-mm hh:mm:ss) can u help me out

am a T-sql guy and dont know any thng aby pl-sql(if its in t-sql i would directly do i with convert and substring)

Upvotes: 0

Views: 8094

Answers (3)

mtwaddell
mtwaddell

Reputation: 189

If I understand your question I think you should be able to update your table in one statement. It's not the most elegant solution, but the following should work.

UPDATE tbl
   SET columnA = DECODE(type, '1', DECODE(INSTR(columnB, '-'), 5, TO_DATE(columnB, 'YYYY-MM-DD'),
                                                                  TO_DATE(columnB, 'MM/DD/YYYY')),
                                   TO_DATE(columnB, 'MMDDYYYY'));

In the above statement I'm assuming you have a column named type that indicates which date format column B is in. The above statement uses the DECODE to determine if the type is 1. Since the type 1 has 3 possible formats , the statement will then try to determine which format columnB is in. To make our job easier we only need to test for the YYYY-MM-DD format as we can use the MM/DD/YYYY format with both 09/22/2011 and 9/22/2011. So we use the INSTR function to determine the location of the first '-' character. If that position is 5, then we know the column is in the YYYY-MM-DD format and can use the appropriate date mask. If the position is not 5, then we know columnB is in the MM/DD/YYYY format. Lastly, if the type isn't 1, then we know the date mask should be MMDDYYYY.

Upvotes: 0

Ben
Ben

Reputation: 52863

You don't.

Firstly you do it properly next time and store dates in a DATE data type; if this is supplied data then you yell1 at your suppliers.

The simplest way to clean your data would be to create a function that tests if a date is in a certain format:

create or replace function is_date ( 
      P_String in varchar2
    , P_Date_Format in varchar2
      ) return number is

   l_date date;

begin

   l_date := to_date(P_String, P_Date_Format);

   return 1;
exception when others then
   return 0;
end;

Next you pick a format model and update just that one.

update my_table
   set date_column = to_date(char_column, 'yyyy-mm-dd')
 where is_date(char_column, 'yyyy-mm-dd') = 1

You then have to pick a different format model and do it all over again until you don't have any records in your date column that are NULL.

1. Yelling may be a bit much, but make sure you're heard.

This could be distilled into a single query with a large CASE statement:

update my_table
   set date_column = case when is_date(char_column, 'yyyy-mm-dd') = 1
                               then to_date(char_column, 'yyyy-mm-dd')
                          when is_date(char_column, 'yyyymmdd') = 1
                               then to_date(char_column, 'yyyymmdd')
                          ...
                          snip
                          ...
                     end

Upvotes: 2

Ya.
Ya.

Reputation: 2627

If I had a problem like this then my first question would be how to programatically detect what date format columnA is for each row. Assuming that it's doable within a reasoanble LOE (I don't know the complete spectrum of your date formats) I'd then see how I could use the CASE expression to detect the format and then format the date accordingly for each case.

Upvotes: 0

Related Questions