Issue with DATE [message #662341] |
Tue, 25 April 2017 11:32 |
|
rajkumar561991
Messages: 1 Registered: April 2017
|
Junior Member |
|
|
Hi,
Am having the issue with adding days with date.
see am using this query
select date,name,dob,(to_date('31-Mar-1985')+31) as Ndate from emp
but what i want to do is
select to_date(dob-(sysdate),'dd-mm-yyyy') from emp --> am getting Number value like '34'
and am storing this value as fDate now fDate='34'
shall i use like this
select date,name,dob,fDate from dual;
it's giving inconstant error!!!
Any idea about this....
|
|
|
|
Re: Add day to a date [message #662343 is a reply to message #662341] |
Tue, 25 April 2017 12:17 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Quote:select to_date(dob-(sysdate),'dd-mm-yyyy') from emp --> am getting Number value like '34'
If dob is a DATE then dob-sysdate is a NUMBER and TO_DATE(NUMBER,...) does not exist but TO_DATE(string,...) exists, so Oracle converts your number to a string implicitly applying TO_CHAR on it. Then, it should not be able to apply the format "dd-mm-yyyy" on it and you should get an error.
Tell us what you expect from this expression. What is a date based on a difference between 2 dates?
And please, post a working Test case: create statements for all objects so that we will be able work to reproduce what you have and post your Oracle version.
Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
[Updated on: Tue, 25 April 2017 12:18] Report message to a moderator
|
|
|
|
Re: Issue with DATE [message #662345 is a reply to message #662341] |
Tue, 25 April 2017 12:45 |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
rajkumar561991 wrote on Tue, 25 April 2017 12:32Hi,
Am having the issue with adding days with date.
see am using this query
select date,name,dob,(to_date('31-Mar-1985')+31) as Ndate from emp
You cannot use TO_DATE without a date format mask. You are just plain lucky it is working for you.
SQL> select to_date('31-Mar-1985')+31 from dual;
select to_date('31-Mar-1985')+31 from dual
*
ERROR at line 1:
ORA-01843: not a valid month
|
|
|
Re: Issue with DATE [message #662359 is a reply to message #662341] |
Wed, 26 April 2017 06:49 |
|
EdStevens
Messages: 1376 Registered: September 2013
|
Senior Member |
|
|
rajkumar561991 wrote on Tue, 25 April 2017 11:32Hi,
Am having the issue with adding days with date.
see am using this query
select date,name,dob,(to_date('31-Mar-1985')+31) as Ndate from emp
In additon to all of the other issues that have been raised, it appears that you want to display a date one month from today ('31-Mar-1985')+31). Do you really want 31 days from today, or one month from today? If the answer is one month, then you should be using the ADD_MONTHS function. Why? because not all months have 31 days. See https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions011.htm#SQLRF00603
|
|
|