CODEWITHSUNDEEP
sqloracle-database
Jason Weng
Jason Weng

Reputation: 21

Oracle sql - convert string to date

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.

Upvotes: 1

Views: 18954

Answers (2)

Ferdinand Gaspar
Ferdinand Gaspar

Reputation: 2073

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

Upvotes: 2

JohnHC
JohnHC

Reputation: 11205

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

Upvotes: 2

Related Questions