OraFAQ Forum: SQL & PL/SQL » Char to Date Conversion

Home » SQL & PL/SQL » SQL & PL/SQL » Char to Date Conversion (10.1.0.3.0)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

Char to Date Conversion [message #576676]

Thu, 07 February 2013 03:25

yuvraaj
Messages: 94
Registered: January 2011
Location: Bangalore, INDIA

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

Report message to a moderator

Re: Char to Date Conversion [message #576677 is a reply to message #576676]

Thu, 07 February 2013 03:28

Littlefoot
Messages: 16960
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

cookiemonster
Messages: 9141
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?

Report message to a moderator

Re: Char to Date Conversion [message #576701 is a reply to message #576691]

Thu, 07 February 2013 06:31

Littlefoot
Messages: 16960
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

OBVIOUSLY, I was wrong. Didn't know that!

Report message to a moderator

Re: Char to Date Conversion [message #576709 is a reply to message #576701]

Thu, 07 February 2013 07:36

Michel Cadot
Messages: 54155
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

Report message to a moderator

Re: Char to Date Conversion [message #576745 is a reply to message #576709]

Thu, 07 February 2013 11:28

yuvraaj
Messages: 94
Registered: January 2011
Location: Bangalore, INDIA

Member

Thanks for all your inputs.

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

Thanks once again.

-YJ

Report message to a moderator

Re: Char to Date Conversion [message #576857 is a reply to message #576676]

Fri, 08 February 2013 10:34

joy_division
Messages: 4265
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.

Report message to a moderator

Previous Topic:	SQL: How to cumulatively sum up values till a condition is satisfied?
Next Topic:	assertions

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue May 21 06:09:32 CDT 2013

Total time taken to generate the page: 0.10561 seconds