The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Week number giving 1 week less number

cp00634326cp00634326 Vertica Customer

Hi team,
I am getting 1 week less when running the query to check.Below is my test.Current week no is 21 but it is showing 20.

vertdba=> SELECT CURRENT_TIMESTAMP
vertdba-> ;

?column?

2020-05-19 03:43:39.792345-05
(1 row)
vertdba=> select TO_CHAR( CURRENT_TIMESTAMP AT TIMEZONE 'UTC'::DATE - INTERVAL '0 WEEKS','YYYYWW')::INT;

TO_CHAR

202020
(1 row)
vertdba=> select week(current_timestamp);

week

21
(1 row)

But what wrong in the below query which is giving week 20.
vertdba=> SELECT TO_CHAR( CURRENT_TIMESTAMP AT TIMEZONE 'UTC'::DATE - (0::varchar(6) || ' WEEKS')::interval,'YYYYWW')::INT;

TO_CHAR

202020
(1 row)

Tagged:

Best Answers

  • cp00634326cp00634326 Vertica Customer
    Answer ✓

    Thanks Brayan ,it worked..The other method you ahve suggested i have tried but it is not subtracting any value.My requirement is to subtract no of weeks from current week. However string 'YYYYIW' is working properly for Weekly .

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file