How to add a select system date statement in the where clause-CodePudding

Need help. I try to add a select clause in where clause in my query so that it can select the data which is same as the device system date .

my code that work: (EXTRACT(YEAR FROM W.BrwDate) = '2021')

I try to add select sysdate from dual; so no need to write the specific year and query can be use for every year . But if i combine the code it no work

WHERE (EXTRACT(YEAR FROM RegYear = (select sysdate extract (year from dual))))

So is that any method can solve my mistake?

CodePudding user response：

Should be

where extract (year from regyear) = extract (year from sysdate)

CodePudding user response：

Probably a bit late, but you have this alternative also

WHERE TRUNC(RegYear,'YYYY') = TRUNC(SYSDATE,'YYYY')

CodePudding user response：

You can use a range comparison:

WHERE RegYear >= TRUNC(SYSDATE, 'YY')
AND   RegYear <  ADD_MONTHS(TRUNC(SYSDATE, 'YY'), 12)

This does not apply a function to your column values (only applying functions on SYSDATE) and will allow Oracle to use an index on the RegYear column.

If you compare for equality of the years using:

WHERE EXTRACT(YEAR FROM RegYear) = EXTRACT(YEAR FROM SYSDATE)

or equality of truncated dates:

WHERE TRUNC(RegYear, 'YY') = TRUNC(SYSDATE, 'YY')

Then Oracle will have to apply the function to every value in the column and will not use an index on RegYear. Instead, to use an index, you would need a separate function-based index on EXTRACT(YEAR FROM RegYear) or TRUNC(RegYear, 'YY') respectively.