• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 353
  • Last Modified:

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
0
NiceMan331
Asked:
NiceMan331
  • 5
  • 3
1 Solution
 
sdstuberCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now