OraFAQ Forum: SQL & PL/SQL » how to convert string to date

Home » SQL & PL/SQL » SQL & PL/SQL » how to convert string to date

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

how to convert string to date [message #291152]

Thu, 03 January 2008 03:21

annu-agi
Messages: 239
Registered: July 2005
Location: Karachi

Senior Member

hi experts

Data stored in distributed form e.g day , month and year and i want to store in one field for that i test the query below .. and at the end i stuck in how to convert whole string in date type data. see the query below and help me out

  1* select to_Date(substr(last_day(to_date(&mmon,'MM')),1,2)||'-'||to_char(to_date(&mmon,'MM'),'Mon')||'-'||to_number(&myear),'DD-MM-YYYY') from dual
SQL> /
Enter value for mmon: 02
Enter value for mmon: 02
Enter value for myear: 2007
old   1: select to_Date(substr(last_day(to_date(&mmon,'MM')),1,2)||'-'||to_char(to_date(&mmon,'MM'),'Mon')||'-'||to_number(&myear),'DD-MM-YYYY') from dual
new   1: select to_Date(substr(last_day(to_date(02,'MM')),1,2)||'-'||to_char(to_date(02,'MM'),'Mon')||'-'||to_number(2007),'DD-MM-YYYY') from dual
select to_Date(substr(last_day(to_date(02,'MM')),1,2)||'-'||to_char(to_date(02,'MM'),'Mon')||'-'||to_number(2007),'DD-MM-YYYY') from dual
                                                                                                 *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

SQL>

regards

anwer

Report message to a moderator

Re: how to convert string to date [message #291154 is a reply to message #291152]

Thu, 03 January 2008 03:27

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Your date format is incorrect. you have dd-Mon-yyyy. But even then, February 29th 2007 isn't valid either.

MHE

Report message to a moderator

Re: how to convert string to date [message #291157 is a reply to message #291152]

Thu, 03 January 2008 03:29

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You query is meaningless.
What is "to_number(2007)"? 2007 IS a number, why do you want to convert it to a number?
What is the purpose of "to_char(to_date(02,'MM'),'Mon')" if you just want to concatenate 02 and 2007?
What is the purpose of "substr(last_day(to_date(02,'MM')),1,2)" if you just want to concatenate this with the previous 02-2007? Why not a final last_day?

This is far too complex (and wrong) that necessary.
Restart from the begining. Try to first explain in words what you want to do BEFORE writing any SQL.

Regards
Michel

Report message to a moderator

Re: how to convert string to date [message #291159 is a reply to message #291157]

Thu, 03 January 2008 03:33

Maaher
Messages: 7065
Registered: December 2001

Senior Member

I'd try something like this:

SELECT LAST_DAY(TO_DATE('&yourmonth'||'-'||'&youryear','mm-yyyy')) x
FROM   dual

First convert the concatenation of the month and the year strings to a real date and then take the last day of that month.

MHE

Report message to a moderator

Re: how to convert string to date [message #291160 is a reply to message #291152]

Thu, 03 January 2008 03:37

rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India

Senior Member

And Error is Because ....

SQL> select  substr(last_day(to_date(02,'MM')),1,2) from dual;

SU
--
29

SQL> select to_char(to_date(02,'MM'),'Mon') from dual;

TO_
---
Feb

SQL> select to_number(2007) from dual;

TO_NUMBER(2007)
---------------
           2007

SQL> select to_Date('29'||'-'||'FEB'||'-'||2007,'DD-MM-YYYY') from dual;
select to_Date('29'||'-'||'FEB'||'-'||2007,'DD-MM-YYYY') from dual
                                     *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected


SQL>  select to_Date('29'||'-'||'FEB'||'-'||'2007','DD-MM-YYYY') from dual;
 select to_Date('29'||'-'||'FEB'||'-'||'2007','DD-MM-YYYY') from dual
                                      *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected


SQL>  SELECT LAST_DAY(TO_DATE('01-FEB-2007','DD-MON-YYYY')) LASTDAY FROM DUAL;

LASTDAY
---------
28-FEB-07

SQL>

Rajuvan.

[Updated on: Thu, 03 January 2008 03:40]

Report message to a moderator

Re: how to convert string to date [message #291161 is a reply to message #291160]

Thu, 03 January 2008 03:43

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

What am I tiring myself out giving leads to OP in order he finds himself the errors when it is so easy to post the solution? Why?

Regards
Michel

Report message to a moderator

Re: how to convert string to date [message #291163 is a reply to message #291161]

Thu, 03 January 2008 03:48

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Michel Cadot wrote on Thu, 03 January 2008 10:43

What am I tiring myself out giving leads to OP in order he finds himself the errors when it is so easy to post the solution? Why?

Regards
Michel

Ok, signing off.

MHE

Report message to a moderator

Re: how to convert string to date [message #291164 is a reply to message #291159]

Thu, 03 January 2008 03:51

annu-agi
Messages: 239
Registered: July 2005
Location: Karachi

Senior Member

thanks gurus

i concentraite on whole .. and i found a solution .. actually i m thinking that without making a complete date like 31-aug-2007 it doesnt works .. thats why i m tring to make complete date .. but mhe give me hint to concentrait first on month and year .. and it works and michale you always simplify whatever jumble i made .. and explane where i m going wrong.... keep doing ... thats always helpfull ..

chow

regards

anwer

Report message to a moderator

Previous Topic:	CLOB data INSERT and READ?
Next Topic:	is it possible to use collect function in 9i

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jul 11 13:55:06 CDT 2025