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

Gordon Linoff · Accepted Answer · 2020-04-06 12:08:10Z

6

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.

answered Apr 6, 2020 at 12:08

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

IamWarmduscher Over a year ago

@Gordon Linoff would you upvote my question? Apparently, I am not allowed to ask any for questions for the time being. The hep section says that it may be due to previously asking unhelpful questions. Can you give me an upvote so I can keep participating here?

Nick · Accepted Answer · 2020-04-06 03:01:46Z

4

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

edited Apr 6, 2020 at 3:01

answered Apr 6, 2020 at 2:42

Nick

147k23 gold badges67 silver badges106 bronze badges

1 Comment

Andronicus Over a year ago

That is so much better than my solution!

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related