Date format

Hi,

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

Comments

  • 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)
    
  • I suggest something slightly simpler... the to_char function.
    CBear=> select to_char('2017-03-22'::date, 'YYYYMM');

    to_char

    201703
    (1 row)

  • much simpler!

Leave a Comment

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.