Postgres: How do I extract year and month from a date?

Question

One of my columns is a date type in the following format: YYYY-MM-DD. I want to extract YYYY-MM. So far, the resources I've come across show me that I can extract either year using SELECT extract(year from order_date)... but I can't figure out how to extract both the year and the month. I tried the following but it didn't work: https://www.w3schools.com/sql/func_mysql_extract.asp

Answer 1

I just want to point out that it is often convenient to leave the value as a date. If so, use date_trunc():

select date_trunc('month', order_date) as yyyymm

If you really want a string, you should accept Nick's answer.

Answer 2

In PostgreSQL you can use TO_CHAR():

SELECT TO_CHAR(order_date, 'YYYY-MM')

Output (if order_date = '2020-04-06'):

2020-04

Note if your column is not already a date or timestamp you will need to cast it to a date or timestamp (e.g. order_date::date).

Demo on dbfiddle