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
NiceMan331Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
sdstuberConnect With a Mentor Commented:
update yourtable set pay_date = last_day(to_date(period,'mm - yyyy'))
0
 
NiceMan331Author Commented:
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 ?
0
 
NiceMan331Author Commented:
in other word
let update the fileds which has correct format , then we will see the null value to correct it
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
sdstuberCommented:
select * from yourtable where not regexp_like(period,'[0-9]{2} - [0-9]{4}')
0
 
johnsoneSenior Oracle DBACommented:
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.
0
 
sdstuberCommented:
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}')
0
 
sdstuberCommented:
really though, this "how to find invalid data" should be a new question anyway
0
 
NiceMan331Author Commented:
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 ?
0
 
sdstuberCommented:
you've already gotten your answer so don't bother now
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.