Home » SQL & PL/SQL » SQL & PL/SQL » how to convert string to date
how to convert string to date [message #291152] Thu, 03 January 2008 03:21 Go to next message
annu-agi
Messages: 199
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
Re: how to convert string to date [message #291154 is a reply to message #291152] Thu, 03 January 2008 03:27 Go to previous messageGo to next message
Maaher
Messages: 7062
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
Re: how to convert string to date [message #291157 is a reply to message #291152] Thu, 03 January 2008 03:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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


Re: how to convert string to date [message #291159 is a reply to message #291157] Thu, 03 January 2008 03:33 Go to previous messageGo to next message
Maaher
Messages: 7062
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
Re: how to convert string to date [message #291160 is a reply to message #291152] Thu, 03 January 2008 03:37 Go to previous messageGo to next message
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>


Thumbs Up
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: how to convert string to date [message #291163 is a reply to message #291161] Thu, 03 January 2008 03:48 Go to previous messageGo to next message
Maaher
Messages: 7062
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
Re: how to convert string to date [message #291164 is a reply to message #291159] Thu, 03 January 2008 03:51 Go to previous message
annu-agi
Messages: 199
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
Previous Topic: CLOB data INSERT and READ?
Next Topic: is it possible to use collect function in 9i
Goto Forum:
  


Current Time: Sun Dec 04 20:50:58 CST 2016

Total time taken to generate the page: 0.07841 seconds