CODEWITHSUNDEEP
sqloracle10g
Jonathan Morningstar
Jonathan Morningstar

Reputation: 431

Using a date as a variable in a where clause

How do you use date as a variable in a where clause? I can't seem to understand how it works.

SELECT MOPACTIVITY.mopid, 
       TO_CHAR(MOPACTIVITY.mopstart, 'yyyy-mm-dd hh24:mi') SOM, 
       TO_CHAR(MOPACTIVITY.mopend, 'yyyy-mm-dd hh24:mi') EOM
FROM MOPUSER.MOPACTIVITY  
WHERE MOPACTIVITY.mopstart BETWEEN '01-JAN-14' AND '31-JAN-14'

What is the correct syntax for the code above? I am trying to run this from the Oracle SQL Developer.

Upvotes: 0

Views: 106

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271241

Your code would work with quotes around the constants:

$a = '2014-01-01 00:00:00';
$b = '2014-01-31 23:59:59';

SELECT MOPACTIVITY.mopid, 
       TO_CHAR(MOPACTIVITY.mopstart, 'yyyy-mm-dd hh24:mi') SOM, 
       TO_CHAR(MOPACTIVITY.mopend, 'yyyy-mm-dd hh24:mi') EOM
FROM MOPUSER.MOPACTIVITY  
WHERE TO_CHAR(MOPACTIVITY.mopstart, 'yyyy-mm-dd hh24:mi') BETWEEN $a AND $b;

In practice, though, it is usually better to convert the constants to the type of the column rather than vice versa:

WHERE MOPACTIVITY.mopstart BETWEEN to_date($a, 'yyyy-mm-dd hh24:mi')  AND
                                   to_date($b, 'yyyy-mm-dd hh24:mi')

By not changing the type of the column, Oracle can more easily take advantage of an index on that column.

Upvotes: 2

Related Questions