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

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