How to set a variable containing the year of a field?-CodePudding

So I have a field CNCL_EFCTV_DT that contains timestamp values i.e. 2022-07-06 06:56:08.656294. What I need to do is set a variable that will return just the year of that timestamp (2022). I tried using SET year = YEAR(CNCL_EFCTV_DT) but an error pops up saying 'invalid identifier'. How can I fix this??

Thanks!!

CodePudding user response：

You can use something like this:

SET year = (SELECT YEAR(CNCL_EFCTV_DT) FROM yourtable WHERE x=y );

If more than one record returns:

SET year = (SELECT TOP 1 YEAR(CNCL_EFCTV_DT) FROM yourtable WHERE x=y );

CodePudding user response：

The table has to be referenced to get column value:

SET year = (SELECT YEAR(NCL_EFCTV_DT) FROM table_name LIMIT 1);

SELECT $year;