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