Year Function - Comparison's of Years

[krbgrx]

I am getting an error message in a Stored Procedure using the Line:

and YEAR(APC.dtEffective) = EffectiveYear

Effective year will have a year in it(Ex: 2000), and apc.dteffective is a date field from a table.

The error message that I am getting is:

PLS-00201: identifier "YEAR" must be declared

The question is how do I code this statement to compare years?

 

[THoey]

To the best of my knowledge, there isn't a YEAR() function in Oracle (looked in the book, didn't see it). You could use the SUBSTR() function to get just the year out of the date. Something like:

and SUBSTR(APC.dtEffective, 8, 2) =
SUBSTR(EffectiveYear, 3, 2)

Hope that helps...

Terry M. Hoey

 

[carp]

Or create your own YEAR function:

CREATE OR REPLACE
FUNCTION year (p_date DATE) RETURN VARCHAR2 AS
BEGIN
RETURN to_char(p_date,'YYYY');
END;

 

[krbgrx]

Thank You

that seemed to work.

So simple if you know how.

 

[CRoberts]

A date field is a special 7 byte field not accessable to SQL. As such, you can not do a SUBSTR on it. All methods of accessing the date other that the default (usually dd-mon-yy) format has to be handle by TO_CHAR or TO_DATE functions with the appropriate format mask. So, if you have a date field and a year number field to compare, you would have to:

TO_CHAR(DateCol, 'YYYY') = TO_CHAR(YearNumCol)

OR

TO_NUMBER(TO_CHAR(DateCol, 'YYYY')) = YearNumCol