Oracle SQL Extract Year from VARCHAR2

Please don’t roast me for not being able to figure this out. I’m a beginner with SQL and I’ve been trying to find a solution and make it work for at least a couple hours in total. I know the date fields should be stored as dates, not varchar2, but I have no control over that.

EXTRACT ( YEAR FROM ( TO_DATE ( 'ENTDAT', 'MM/DD/YYYY' ))) = 2018
"ORA-01858: a non-numeric character was found where a numeric was expected"

​x
 
EXTRACT ( YEAR FROM ( TO_DATE ( 'ENTDAT', 'MM/DD/YYYY' ))) = 2018"ORA-01858: a non-numeric character was found where a numeric was expected"​

I feel like I’m close with this, just missing some magic and hoping for an assist here.

Answer

'ENTDAT' is a string literal.

ENTDAT (without the quotes) could be a column name.

So if your column name is ENTDAT you probably wanted:

SELECT *
FROM   your_table
WHERE  EXTRACT ( YEAR FROM ( TO_DATE ( ENTDAT, 'MM/DD/YYYY' ))) = 2018

 
SELECT *FROM   your_tableWHERE  EXTRACT ( YEAR FROM ( TO_DATE ( ENTDAT, 'MM/DD/YYYY' ))) = 2018​

Advertisement

Answer