Link to home
Start Free TrialLog in
Avatar of NiceMan331
NiceMan331

asked on

converting string to date

dear all
i have one table having 2 tables
period  varchar2   ,  
pay_date  date

period storing values of periods like this format
'01 - 2005'    ( to refer to jan 2005)
pay date is empty
how i would update all pay_date to be last date of the month
if period = '01 - 2005'  then pay_date = '31-jan-05'

thanx
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of NiceMan331
NiceMan331

ASKER

it is ok for a single raw
but when i update all , it return with error
not a valid month
how do i check if one raw is out of the said format ?
in other word
let update the fileds which has correct format , then we will see the null value to correct it
select * from yourtable where not regexp_like(period,'[0-9]{2} - [0-9]{4}')
That regular express will only check if it is numeric, not if it is a valid month.  For example, this has one valid row and one invalid:

WITH t (period) 
     AS (SELECT '01 - 2014' 
         FROM   dual 
         UNION ALL 
         SELECT '13 - 2014' 
         FROM   dual) 
SELECT * 
FROM   t 
WHERE  NOT Regexp_like(period, '[0-9]{2} - [0-9]{4}') 

Open in new window


However, the invalid row will not be returned.

The way that I would do this is with a PL/SQL block, trap the error and ignore the row with the bad date.  Will be slower, but will get around the problem.  Alternately,  you could write a function that was a couple of lines long to check for a valid date and use that to find valid records.
you're right, it's not specific enough, but I figured it would probably point in the right direction.

To be precise though, this should work

select * from yourtable where not regexp_like(period,'(0[1-9]|1[012]) - [0-9]{4}')
really though, this "how to find invalid data" should be a new question anyway
thanx for both
i got the invalid data in another way
the period was updated its value from table containing all periods values
so , select * from my_table where period not in (select period from period_table)
then it done
an execuse from johnson , i have to accept the solution of sdstubar with full thanx
is it fare for you ?

really though, this "how to find invalid data" should be a new question anyway

shall i open a new question for it ?
you've already gotten your answer so don't bother now