Home » SQL & PL/SQL » SQL & PL/SQL » Char to Date Conversion (10.1.0.3.0)
Char to Date Conversion [message #576676] Thu, 07 February 2013 03:25 Go to next message
Yuvraaj
Messages: 118
Registered: January 2011
Location: California, USA
Senior Member
Hi,

Need quick help in converting a string to date, my string looks like '01022013' '04022013', I tried to_date('01022013', 'dd-mm-yyyy') but couldn't work.

Thanks in advance
YJ
Re: Char to Date Conversion [message #576677 is a reply to message #576676] Thu, 07 February 2013 03:28 Go to previous messageGo to next message
Littlefoot
Messages: 19886
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Format mask is, OBVIOUSLY, wrong.
01022013 is NOT dd-mm-yyyy. Where do you see dashes between (01 and 02) or (02 and 2013)? Try with ddmmyyyy instead.

[Updated on: Thu, 07 February 2013 03:28]

Report message to a moderator

Re: Char to Date Conversion [message #576691 is a reply to message #576677] Thu, 07 February 2013 05:50 Go to previous messageGo to next message
cookiemonster
Messages: 11285
Registered: September 2008
Location: Rainy Manchester
Senior Member
Actually it's not obviously wrong, oracle doesn't care it you skip the dashes:
SQL> SELECT to_date('01022013', 'dd-mm-yyyy') FROM dual
  2  /

TO_DATE('010220
---------------
20130201 000000



So yuvraaj - in what way is it not working?
Re: Char to Date Conversion [message #576701 is a reply to message #576691] Thu, 07 February 2013 06:31 Go to previous messageGo to next message
Littlefoot
Messages: 19886
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OBVIOUSLY, I was wrong. Didn't know that!
Re: Char to Date Conversion [message #576709 is a reply to message #576701] Thu, 07 February 2013 07:36 Go to previous messageGo to next message
Michel Cadot
Messages: 59991
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, Oracle does not care about constant characters but only when constants are only 1 character unless you omit the whole constant part:
SQL> select to_date('01#02|2013', 'dd-mm-yyyy') from dual;
TO_DATE('01#02|2013
-------------------
01/02/2013 00:00:00

SQL> select to_date('01#022013', 'dd-mm-yyyy') from dual;
TO_DATE('01#022013'
-------------------
01/02/2013 00:00:00

SQL> select to_date('01022013','DD------MM-----YYYY') from dual;
TO_DATE('01022013',
-------------------
01/02/2013 00:00:00

SQL> select to_date('01Michel02Cadot2013','DD------MM-----YYYY') from dual;
select to_date('01Michel02Cadot2013','DD------MM-----YYYY') from dual
               *
ERROR at line 1:
ORA-01861: literal does not match format string

But if the constant string is between " then it is a real constraint:
SQL> select to_date('01022013','dd"-"mm"-"yyyy') from dual;
select to_date('01022013','dd"-"mm"-"yyyy') from dual
               *
ERROR at line 1:
ORA-01861: literal does not match format string


SQL> select to_date('01#02/2013', 'dd"-"mm"-"yyyy') from dual;
select to_date('01#022013', 'dd"-"mm"-"yyyy') from dual
               *
ERROR at line 1:
ORA-01861: literal does not match format string


SQL> select to_date('01-02-2013', 'dd"-"mm"-"yyyy') from dual;
TO_DATE('01-02-2013
-------------------
01/02/2013 00:00:00


Regards
Michel
Re: Char to Date Conversion [message #576745 is a reply to message #576709] Thu, 07 February 2013 11:28 Go to previous messageGo to next message
Yuvraaj
Messages: 118
Registered: January 2011
Location: California, USA
Senior Member
Thanks for all your inputs.

SELECT to_date('01022013', 'ddmmyyyy') FROM dual; worked Smile


Thanks once again.

-YJ



Re: Char to Date Conversion [message #576857 is a reply to message #576676] Fri, 08 February 2013 10:34 Go to previous message
joy_division
Messages: 4559
Registered: February 2005
Location: East Coast USA
Senior Member
yuvraaj wrote on Thu, 07 February 2013 04:25
Hi,

Need quick help in converting a string to date, my string looks like '01022013' '04022013', I tried to_date('01022013', 'dd-mm-yyyy') but couldn't work.

Thanks in advance
YJ


It works for me. Based on the answers and your final posting, you cannot possibly be telling us the whole truth, since
SQL> select to_date('01022013', 'dd-mm-yyyy')  from dual;

TO_DATE('01022013',
-------------------
02/01/2013 00:00:00
works.
Previous Topic: SQL: How to cumulatively sum up values till a condition is satisfied?
Next Topic: what's wrong in this script
Goto Forum:
  


Current Time: Fri Dec 19 00:49:02 CST 2014

Total time taken to generate the page: 0.19700 seconds