Home » SQL & PL/SQL » SQL & PL/SQL » Issue with DATE
Issue with DATE [message #662341] Tue, 25 April 2017 11:32 Go to next message
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: Issue with DATE [message #662342 is a reply to message #662341] Tue, 25 April 2017 12:15 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Re: Add day to a date [message #662343 is a reply to message #662341] Tue, 25 April 2017 12:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
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: Add day to a date [message #662344 is a reply to message #662343] Tue, 25 April 2017 12:20 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
DATE+NUMBER=<new DATE>
  1* SELECT HIREDATE, HIREDATE+30, EMPNO FROM EMP
SQL> /

HIREDATE	    HIREDATE+30 	     EMPNO
------------------- ------------------- ----------
1980-12-17 00:00:00 1981-01-16 00:00:00       7369
1981-02-20 00:00:00 1981-03-22 00:00:00       7499
1981-02-22 00:00:00 1981-03-24 00:00:00       7521
1981-04-02 00:00:00 1981-05-02 00:00:00       7566
1981-09-28 00:00:00 1981-10-28 00:00:00       7654
1981-05-01 00:00:00 1981-05-31 00:00:00       7698
1981-06-09 00:00:00 1981-07-09 00:00:00       7782
1987-04-19 00:00:00 1987-05-19 00:00:00       7788
1981-11-17 00:00:00 1981-12-17 00:00:00       7839
1981-09-08 00:00:00 1981-10-08 00:00:00       7844
1987-05-23 00:00:00 1987-06-22 00:00:00       7876

HIREDATE	    HIREDATE+30 	     EMPNO
------------------- ------------------- ----------
1981-12-03 00:00:00 1982-01-02 00:00:00       7900
1981-12-03 00:00:00 1982-01-02 00:00:00       7902
1982-01-23 00:00:00 1982-02-22 00:00:00       7934

14 rows selected.
Re: Issue with DATE [message #662345 is a reply to message #662341] Tue, 25 April 2017 12:45 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
rajkumar561991 wrote on Tue, 25 April 2017 12:32
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

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 Go to previous message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
rajkumar561991 wrote on Tue, 25 April 2017 11:32
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


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
Previous Topic: SQL Tunning - Please Help Me
Next Topic: NOT LIKE operation on the numbers columns
Goto Forum:
  


Current Time: Fri Mar 29 08:10:32 CDT 2024