Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2011
    Posts
    4

    Red face Unanswered: query translation from SQL Server to PostgreSQL

    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!

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    use
    Code:
    extract(year from date_renew)

  3. #3
    Join Date
    Dec 2011
    Posts
    4
    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)

    ??
    Last edited by sqlsuj; 12-14-11 at 09:44. Reason: the reason i ask is that there are other years in the data 2010, 2011 but i am looking for 2012 to indicate date_renew

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Code:
    and extract(year from date_renew)  = 2012
    Extract returns a numeric value. See the manual for details

  5. #5
    Join Date
    Dec 2011
    Posts
    4
    Thanks! this worked

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •