i am having problems with converting a varchar(yyyymmdd) to date(yyyymmdd).
i have a procedure with a parameter (pdate varchar2, yyyymmdd format) which needed to be converted to date type in yyyymmdd format as well. so far i tried.
vdate date;
vdate := (to_date(SUBSTR(pdate,1,4)+SUBSTR(pdate,5,2)+SUBSTR(pdate,7,2), 'yyyymmdd'));
this threw a error of ORA-01840: input value not long enough for date format.
any help would be appreciated.
Just use a to_date
select to_date(MyDate, 'yyyymmdd')
from mytable
Test with:
select to_date('20170831','yyyymmdd')
from dual
Also, to concatenate in Oracle, use a double pipe ||
select 'Chicken'||'Food'
from dual
If pdate has other characters after yyyymmdd, and yyyymmdd is in the beginning of the whole text, you can just use
SELECT TO_DATE(SUBSTR(pdate,1,8), 'yyyymmdd')
FROM yourtable;
Example
SELECT TO_DATE(SUBSTR('20170831 10:30am',1,8), 'yyyymmdd')
FROM dual;
Otherwise, you can directly use TO_DATE() as suggested by most that replied
+to||Consider:SELECT (SUBSTR('20170101',1,4)||SUBSTR('20170101',5,2)||SUBSTR('20170101',7,2)) , to_date(SUBSTR('20170101',1,4)||SUBSTR('20170101',5,2)||SUBSTR('20170101',7,2),'YYYYMMDD') , To_date('20170101','YYYYDDMM') from dual;to_date(pdate, 'yyyymmdd'), assuming that your pdate contains the full set of information. In fact, I'm pretty sure you can get rid of your procedure and just use theto_date()function!||in Oracle, not with+. What you are doing is that you are extracting the year, month and date, as strings (four digits, two digits, two digits). Then you add them. Behind the scenes, Oracle converts them toNUMBERand adds them. Then you applyto_date()which takes avarchar2input, so Oracle - again behind the scenes - converts the result of the addition to a four-character string. Which is not long enough to become a date (and it would be a wrong one anyway!)