Home » SQL & PL/SQL » SQL & PL/SQL » dates and null (Win XP)
dates and null [message #378456] Tue, 30 December 2008 08:53 Go to next message
keizersoz
Messages: 16
Registered: April 2006
Junior Member
Hello,

After running following select:

SELECT 
 SYSDATE-NULL
FROM
 DUAL

From the data grid it appears that the result is not null. How is this possible? Moreover when I run:

SELECT 
 NVL(SYSDATE-NULL,1)
FROM
 DUAL


I get ORA-00932.

thank you in advance for your assitance.

Kind regards,
Keizersoz
Re: dates and null [message #378460 is a reply to message #378456] Tue, 30 December 2008 09:10 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
From the data grid it appears that the result is not null. How is this possible?

And what it appears to be? Also, what is your Oracle version (four decimals)?

When I ran it in Oracle 11.1.0.6, I got this:
SQL> SELECT SYSDATE-NULL
  2  FROM DUAL;

SYSDATE-N
---------


1 row selected.

SQL> SELECT NVL(SYSDATE-NULL,1)
  2  FROM DUAL;
SELECT NVL(SYSDATE-NULL,1)
                        *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER

The first SELECT: the result is (and shall be) NULL.
The second SELECT: is not the error message clear. Oracle tries to implicitly convert the number 1 to the data type of the first argument (DATE). It fails, as it does not conform the NLS_DATE_FORMAT mask.
Re: dates and null [message #378483 is a reply to message #378456] Tue, 30 December 2008 10:41 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
hi,

the first sql is giving you blank because you are subtracting sysdate with null and if you add/ substract/ multiply or divide anything with null then the result would be NULL.

the second query can be modified as
SELECT NVL(to_number(SYSDATE-NULL),1) change_the_datatype FROM DUAL
you can use nvl when both the parameters have same same datatype. instead you can use to_char.

Regards
Ashu

[Updated on: Tue, 30 December 2008 10:41]

Report message to a moderator

Re: dates and null [message #378484 is a reply to message #378483] Tue, 30 December 2008 10:58 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for confirming what flyboy already clearly explained and demonstrated, much more clearly than you do.

Regards
Michel
Previous Topic: user and password in DBURI
Next Topic: Selecting records which dont have referencing records
Goto Forum:
  


Current Time: Mon Dec 05 13:19:54 CST 2016

Total time taken to generate the page: 0.06743 seconds