CODEWITHSUNDEEP
sqloracle-databaseplsql
uhs
uhs

Reputation: 848

Comparing time stamps in oracle

Suppose if i declare 2 timestamp variable . Lets say 

DECLARE 

var_t1 timestamp :=null;
var_t1 timestamp :=null;
BEGIN 
---Suppose if i assign a timestamp to var_t1 . 

select some_date into var_t1 from sometable where id='some_id';

--IF(var_t2<>var_t2) , this will be always false .

IF(var_t1 <> var_t2 ) THEN
  dbms_output.put_line('Inside if');

END IF;

END;

If any one variable is null, always if return false.

Upvotes: 1

Views: 74

Answers (3)

mrkovec
mrkovec

Reputation: 376

and for completeness' sake i will add a "NVL" solution

declare 
    yearone timestamp :=to_date('1','yyyy');
    var_t1 timestamp :=null;
    var_t2 timestamp :=sysdate;
begin 
    if nvl(var_t1, yearone) <> nvl(var_t2, yearone) then
        dbms_output.put_line('Inside if');
    end if;
end;
/

Upvotes: 1

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59622

Just an addon to Lalit answer:

var_t1 <> var_t2 or var_t1 is null or var_t2 is null

is equivalent to 

LNNVL(var_t1 = var_t2)

just shorter. But note, you can use LNNVL only in WHERE clause of an SELECT, not in PL/SQL IF LNNVL() (don't ask what is the reason for this limitation - I don't know).

Upvotes: 1

Lalit Kumar B
Lalit Kumar B

Reputation: 49112

If any one variable is null, always if return false.

You are comparing a value with NULL. It is neither TRUE nor FALSE. It is a tri-valued logic, which is unknown.

  • nothing is equal to null
  • nothing is NOT equal to null

More meaningful code would be:

IF a <> b or (a is null and b is not null) or (a is not null and b is null)
THEN

UPDATE Adding an example.

SQL> set serveroutput on
SQL> DECLARE
  2    A NUMBER;
  3    b NUMBER;
  4  BEGIN
  5    A    :=NULL;
  6    B    :=10;
  7    IF a <> b OR (a IS NULL AND b IS NOT NULL) OR (a IS NOT NULL AND b IS NULL) THEN
  8      dbms_output.put_line('False');
  9    END IF;
 10  END;
 11  /
False

PL/SQL procedure successfully completed.

SQL>

Upvotes: 1

Related Questions