CODEWITHSUNDEEP
mysqloracle-database
Jiby Jose
Jiby Jose

Reputation: 3845

MySQL Query to Oracle Query Conversion

Consider an sql query like 

SELECT a, b>now() as expired 
FROM TABLE;

where b is a date and checking with current date.

I found out that now() should be replaced with SYSDATE

but how to write 

 b > (SYSDATE) as expired

for Oracle?

Upvotes: 0

Views: 181

Answers (1)

user330315
user330315

Reputation:

Oracle does not have a boolean datatype (neither does MySQL but it simply treats any number not equal to zero as "true"), so you need return a number indicating the expiration as 0 and 1

select a, 
       case 
          when b > sysdate then 1
          else 0
       end as expired
from the_table;

Note that Oracle's DATE datatype includes the time. So SYSDATE returns something like 2013-04-04 14:43:12. You probably want to use trunc() on the comparison:

select a, 
       case 
          when trunc(b) > trunc(sysdate) then 1
          else 0
       end as expired
from the_table;

Of course you can return anything in the case statement, not only numbers

select a, 
       case 
          when trunc(b) > trunc(sysdate) then 'expired'
          else 'active'
       end as expired
from the_table;

Upvotes: 3

Related Questions