PostgreSQL convert month name to number

问题:

I have a query that returns a field as month and year for example "Nov 2015" I want to get the number for the month, here I have to get number as "11".

回答1:

You can use the to_date function that can parse input in a certain format into a 'real' date. Then with this date you can extract the month from it.

select EXTRACT(MONTH FROM to_date('Nov 2015', 'Mon YYYY'))

See http://www.postgresql.org/docs/current/static/functions-formatting.html for the formatting syntax and http://www.postgresql.org/docs/current/static/functions-datetime.html for the other datetime functions etc..

回答2:

to_char()

select to_char(to_date('Nov 2015', 'Mon YYYY'), 'mm') month_no

and the query can be

select to_char(to_date(your_date_col, 'Mon YYYY'), 'mm') month_no
from table_name

回答3:

You can use the function to_timestamp(text, text) and extract(field from timestamp):

SELECT EXTRACT(MONTH FROM TO_TIMESTAMP("Nov 2015", "Mon YYYY"));

see the documentation for to_timestamp and extract

回答4:

If your date is timestamp , for example:"2017-01-24 23:00:00-03" (this is timestamp with time zone)

select to_char(your_date, 'MM') from your_table

Then the result would be in this case: "01"