Re: How can I replace the year of the created_at column with the current year dynamically ?

Steve Crawford <scrawford@xxxxxxxxxxxxxxxxxxxx> · Wed, 02 Jul 2014 08:42:43 -0700



    On 07/01/2014 11:27 PM, Arup Rakshit
      wrote:

    
        Here is my try :
        

        staging::=>
          select  to_char(created_at,'DD/MM') || '/' ||
          to_char(now(),'YYYY') as when from users;
            when  
           
        ------------
         24/02/2014
        ...
         20/02/2014
        (15 rows)
        
        
        Can the same be done using any other
          clever trick ? 
        

    No tricks are springing to mind but a warning is. The above will
    produce illegal dates whenever you are an inconvenient number of
    years past February 29. I think this will fix that issue:

    
    select created_at + ((extract(year from now()) - extract(year from
    created_at)) * '1 year'::interval);

    
    Note that the above returns a date (assuming that created_at is a
    date). You may need to apply to_char to format to your desired
    specification.

    
    Cheers,

    Steve