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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
in other word
let update the fileds which has correct format , then we will see the null value to correct it
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:
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.
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}')
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}')
To be precise though, this should work
select * from yourtable where not regexp_like(period,'(0[1-9
really though, this "how to find invalid data" should be a new question anyway
ASKER
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 ?
shall i open a new question for it ?
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
ASKER
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 ?