Re: to_char function returning wrong data

Sreeni Survi <sreenisurvi@gmail.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