0

I previously ask about converting strings to dates and formatting them.. split string based on character position in ORACLE 11g SQL

Here is the solution I have come up with where WKENDING is VARCHAR2 and RYEAR is date The WKENDING has data that looks like '523'(mmdd) and RYEAR is '2012'..

UPDATE OB_SEL_LST84_AGG_WKEND  SET WKENDYEAR =  (TO_DATE((TO_DATE(substr(WKENDING,3,2)),'dd')||(TO_DATE(substr(WKENDING,0,1)),'mon')||(TO_DATE(TO_CHAR(RYEAR)),'yyyy')),'dd-mon-yyyy');

I am now getting an error 'ORA-00907: missing right parenthesis', I've double checked the parenthesis a couple of times and they look right to me.. any help would be great.. thanks!

UPDATE - After looking at the syntax of what I have above I thought that maybe there are too many TO_DATE attempted conversions going on. So, I shortened it to this.. 

UPDATE OB_SEL_LST84_AGG_WKEND  SET WKENDYEAR =  (TO_DATE((substr(WKENDING,3,2))||(substr(WKENDING,0,1))||TO_CHAR(RYEAR)),'dd-mon-yyyy');

I'm still getting the missing parenthesis error though.. ARGH!

Community
  • 1
  • 1
SeattleGray
  • 95
  • 1
  • 3
  • 10

1 Answers1

1

Since the data type of the wkendyear column is DATE, you should just need to

UPDATE OB_SEL_LST84_AGG_WKEND  
   SET wkendyear = to_date( lpad(WKENDING,4,'0') || RYEAR, 'mmddyyyy' )

This assumes, of course, that all your string data can be converted into a valid date. As soon as you have a wkending of 0229 and a ryear of 2013 (or some other combination of strings that are not a valid date), the TO_DATE function is going to throw an exception. That's one of the reasons that storing dates in VARCHAR2 columns is generally problematic.

If not all of your data can be converted correctly to a DATE, you can create a function that attempts to do the conversion and returns a NULL if there is an exception. For example

CREATE OR REPLACE FUNCTION my_to_date( p_str IN VARCHAR2, p_format IN VARCHAR2 )
  RETURN DATE
IS
  l_dt DATE;
BEGIN
  l_dt := to_date( p_str, p_format );
  RETURN l_dt;
EXCEPTION
  WHEN others THEN
    RETURN NULL;
END;

Your UPDATE statement would then become

UPDATE OB_SEL_LST84_AGG_WKEND  
   SET wkendyear = my_to_date( lpad(WKENDING,4,'0') || RYEAR, 'mmddyyyy' )

You can also look for rows where the string cannot be converted to a date

SELECT *
  FROM OB_SEL_LST84_AGG_WKEND  
 WHERE my_to_date( lpad(WKENDING,4,'0') || RYEAR, 'mmddyyyy' ) IS NULL
   AND (wkending IS NOT NULL or ryear IS NOT NULL)
Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • Tried your solution and got an error 'ORA-01843: not a valid month'.. thinking that its because of the varchar2 datatype? – SeattleGray Jun 27 '12 at 18:05
  • @SeattleGray - Most likely, the problem is that some of the rows of string data cannot be converted into a valid date. Updated my answer with code that converts the data that it can and identifies rows that have problems. – Justin Cave Jun 27 '12 at 18:10
  • Sorry mate.. I compiled the function.. and ran the update statement you provided and then the select statement.. still no luck.. I am stumped.. – SeattleGray Jun 27 '12 at 18:30
  • just looking at the data again.. realized that your function did work.. only problem was that my data is not '0523' like I thought but '523' and the ones your function worked on were those that are like '1001' or '1101' or '1201'.. sorry, should have checked before.. So, how do I account for the missing '0'? – SeattleGray Jun 27 '12 at 18:51
  • @SeattleGray - Assuming it is just the leading '0' that is missing, I updated my answer to pad the `wkending` to 4 characters. – Justin Cave Jun 27 '12 at 18:55
  • Well, I gave it a try and it works great.. but now I am losing the dates that begin with something other than '0'.. how would I go about fixing that issue? – SeattleGray Jun 27 '12 at 20:09
  • @SeattleGray - The `LPAD` call won't change a `wkending` string that has 4 characters. Can you give an example of a string that is not working as expected? – Justin Cave Jun 27 '12 at 20:17
  • well, it looks like I have some data issues unrelated to the problem you are helping me with.. Thanks a bunch for the help.. – SeattleGray Jun 27 '12 at 20:49