Date format

Comments

• Options

  MichaelJK Employee

  March 2017

  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)
  

  0
• Options

  ChuckBear

  March 2017

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

  to_char

  201703
  (1 row)

  1
• Options

  MichaelJK Employee

  March 2017

  much simpler!

  0