Date format

Ckishor

Hi,

Is it possible to get date in "201703" format from Vertica where 2017 is year and 03 is the months??

MichaelJK

I assume you want to extract the year and month from a given date and output it in the format YYYYMM? If so, this is what I came up with:

=> SELECT 
      RIGHT('0000' || DATE_PART('YEAR', NOW())::VARCHAR(4), 4) 
      || RIGHT('00' || DATE_PART('MONTH', NOW())::VARCHAR(2), 2);

 ?column?
----------
 201703
(1 row)

Here are a few variations on the same theme:

=> SELECT RIGHT('0000'|| DATE_PART('YEAR', CURRENT_DATE)::VARCHAR(4), 4) 
    || RIGHT('00'|| DATE_PART('MONTH', CURRENT_DATE)::VARCHAR(2), 2);
 ?column?
----------
 201703
(1 row)

=> SELECT RIGHT('0000'|| DATE_PART('YEAR', TIMESTAMP '2017-04-23')::VARCHAR(4), 4) 
     || RIGHT('00'|| DATE_PART('MONTH', TIMESTAMP '2017-04-23')::VARCHAR(2), 2);
 ?column?
----------
 201704
(1 row)

=> SELECT RIGHT('0000'|| DATE_PART('YEAR', TIMESTAMP '0003-03-15 AD')::VARCHAR(4), 4) 
   || RIGHT('00'|| DATE_PART('MONTH', TIMESTAMP '0003-03-15 AD')::VARCHAR(2), 2);
 ?column?
----------
 000303
(1 row)

ChuckBear

I suggest something slightly simpler... the to_char function.
CBear=> select to_char('2017-03-22'::date, 'YYYYMM');

to_char

201703
(1 row)

MichaelJK

much simpler!