Re: to_char function returning wrong data

Sreeni Survi <sreenisurvi(at)gmail(dot)com> writes:
> Below code has caused my data to be wiped off a table as my where clause
> depends on the below returned value.

> *select to_char(current_date - interval '5 weeks','IYYYWW') ;*
> *201953*

(For the archives, current_date - interval '5 weeks' is currently
'2018-12-31 00:00:00')

There's nothing wrong with to_char; it did what you told it to.

The problem here is that you're using ISO year numbering along with
non-ISO week numbering. You should have written 'IYYYIW', which
would give consistent results:

regression=# select to_char(current_date - interval '4 weeks','IYYYIW') ;
to_char
---------
201902
(1 row)

regression=# select to_char(current_date - interval '5 weeks','IYYYIW') ;
to_char
---------
201901
(1 row)

regression=# select to_char(current_date - interval '6 weeks','IYYYIW') ;
to_char
---------
201852
(1 row)

regards, tom lane