Hi ,

Ive been trying to figure this out for ages, i have this part of a query which I wrote in SQL server:

--------------------------------------------------------------------------
renewal(locationid, date_purchased, date_renew, date_updated, purchase_date_order, last_update_order)
as (
select locationid, date_purchased, date_renew, date_updated,

ROW_NUMBER() over (partition by locationid order by date_purchased) as purchase_date_order,
ROW_NUMBER() over (partition by locationid order by date_updated desc) as last_update_order
from t_owner_product with(nolock)
where productid=1 and status in (1,3) and 2012=YEAR(date_renew)),

--------------------------------------------------------------------------

I’m trying to run in PostgreSQL but it needs to be slightly translated to work.

It’s the YEAR function in particular on the last line and I was wondering if anyone knows what I could write instead?

The current error message i get is:

ERROR: function year(timestamp without time zone) does not exist
LINE 29: where productid=1 and status in (1,3) and 2012=YEAR(date_ren...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.

Any help would be appreciated!

use

Hi!

Thanks for responding, would you be able to show me how to enter that bit? I did as below and it came back with another error message

where productid=1 and status in (1,3) and extract(year from date_renew)),

ERROR message:

ERROR: argument of AND must be type boolean, not type double precision
LINE 29: where productid=1 and status in (1,3) and extract(year from ...

also does extract(year from date_renew) do the sam ething as i was trying to do with my original WHERE statement:

where 2012=YEAR(date_renew)

??

Extract returns a numeric value. See the manual for details

Thanks! this worked