Reputation: 431
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
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