Home » SQL & PL/SQL » SQL & PL/SQL » Addition date and varchar2
icon7.gif  Addition date and varchar2 [message #399338] Tue, 21 April 2009 13:07 Go to next message
joeslee
Messages: 18
Registered: February 2008
Location: Bangalore
Junior Member

Hi,
Is there any possibilities to add varchar2 and date type..In my below example..I am trying to add L_disc_type (Varchar2) and L_confdate (date).

Ex:
to_date((L_disc_type+ L_confdate),'YYYYMMDD')


If Yes,please tell me how to proceed. Razz

Thanks,
Joeslee
Re: Addition date and varchar2 [message #399339 is a reply to message #399338] Tue, 21 April 2009 13:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How could we know what is inside your varchar2 column if you don't tell us?

Post a working Test case: create table and insert statements along with the result you want with these data.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: Addition date and varchar2 [message #399342 is a reply to message #399338] Tue, 21 April 2009 13:31 Go to previous messageGo to next message
joeslee
Messages: 18
Registered: February 2008
Location: Bangalore
Junior Member

L_disc_type have a single digit character like M, S , T etc..

and L_confdate has date. Razz
Re: Addition date and varchar2 [message #399343 is a reply to message #399338] Tue, 21 April 2009 13:44 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
What is the sum of 'M' + 2009-04-02???????????


You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/

[Updated on: Tue, 21 April 2009 13:45]

Report message to a moderator

Re: Addition date and varchar2 [message #399347 is a reply to message #399338] Tue, 21 April 2009 14:17 Go to previous messageGo to next message
Habeeb
Messages: 57
Registered: August 2000
Member
SQL> select sysdate-to_date('01-01-06', 'dd-mm-yy') from dual;

SYSDATE-TO_DATE('01-01-06','DD-MM-YY')
--------------------------------------
                             1206.5977

SQL> select sysdate+to_date('01-01-06', 'dd-mm-yy') from dual;
select sysdate+to_date('01-01-06', 'dd-mm-yy') from dual
              *
ERROR at line 1:
ORA-00975: date + date not allowed
Re: Addition date and varchar2 [message #399348 is a reply to message #399338] Tue, 21 April 2009 14:20 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
Do NOT hijack other people's threads.
Start your own.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Please, please, please Read & Follow Posting Guidelines above.
Go to the section labeled "Practice" & do as directed.
Re: Addition date and varchar2 [message #399349 is a reply to message #399348] Tue, 21 April 2009 14:29 Go to previous messageGo to next message
Habeeb
Messages: 57
Registered: August 2000
Member
With all due respect will you please let me know, what is that I did wrong?
I am not starting a new thread, I think I was replying to the orignal post.

Habeeb
Re: Addition date and varchar2 [message #399350 is a reply to message #399338] Tue, 21 April 2009 14:39 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
>With all due respect will you please let me know, what is that I did wrong?

Oracle's behavior is some cases version dependent.
The Posting Guideline state "Post exact Database (4 decimal places, please) and OS version."


>I am not starting a new thread, I think I was replying to the orignal post.
Please elaborate how, what & why your post is relevant to original problem.

Re: Addition date and varchar2 [message #399353 is a reply to message #399342] Tue, 21 April 2009 14:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
joeslee wrote on Tue, 21 April 2009 20:31
L_disc_type have a single digit character like M, S , T etc..

and L_confdate has date. Razz

Michel Cadot wrote on Tue, 21 April 2009 20:15
How could we know what is inside your varchar2 column if you don't tell us?

Post a working Test case: create table and insert statements along with the result you want with these data.
Also always post your Oracle version (4 decimals).


Regards
Michel



Re: Addition date and varchar2 [message #399356 is a reply to message #399353] Tue, 21 April 2009 15:25 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

With all due respect will you please let me know, what is that I did wrong?



You have a logical problem here.

Subtracting one date from another works. Because there you get the DIFFERENCE between two points in time.

Which is logical plausible. If you take the simplified example of all existing time just being one week, then the DIFFERENCE between the absolute "Monday" point in time and the absolute "Wednesday" point in time would be "two days".

But it wouldn't make any logical sense do ADD Wednesday to Monday.



Re: Addition date and varchar2 [message #399357 is a reply to message #399356] Tue, 21 April 2009 15:32 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Why not? It is Thursday.
Re: Addition date and varchar2 [message #399360 is a reply to message #399357] Tue, 21 April 2009 15:51 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Yeah. And when you add 2009-01-10 to 2009-01-01 you get 4018-02-11. Laughing

I should probably put something like that in a project management software. Then, once I get assigned a few task to do, I have multiple millennia to complete them, and thus will finally have solved all deadline problems. Very Happy
Previous Topic: Distinct with NULL
Next Topic: hierarchical data association with high volume table
Goto Forum:
  


Current Time: Tue Dec 06 14:24:54 CST 2016

Total time taken to generate the page: 0.12576 seconds