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

On Wednesday, July 02, 2014 08:42:43 AM Steve Crawford wrote:
> 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

Thanks Steve. Your warning is 100% valid. *created_at* is a *datetime* data
type.

--
================
Regards,
Arup Rakshit
================
Debugging is twice as hard as writing the code in the first place. Therefore,
if you write the code as cleverly as possible, you are, by definition, not
smart enough to debug it.

--Brian Kernighan