extract year_month from a date column along with other columns in a sql table

Solution 1:

The syntax for EXTRACT is

EXTRACT( <date_or_time_part> FROM <date_or_time_expr> )

And among the <date_or_time_part> are year and month

So to get the yearmonth you need 2 extracts.

CONCAT(EXTRACT(year FROM visit_date),'-', LPAD(EXTRACT(month FROM visit_date), 2, 0)) 

A shorter method is to use TO_CHAR with a format.

TO_CHAR(visit_date,'YYYY-MM') 

Solution 2:

Luk's answer is good, you can use the YEAR and MONTH shortcut functions, and use the || concat token to make it all smaller, and LPAD:

SELECT 
    '2016-01-01'::date as visit_date,
    CONCAT(EXTRACT(year FROM visit_date),'-', LPAD(EXTRACT(month FROM visit_date), 2, 0)) as way_1,
    year(visit_date) || '-' || month(visit_date) as way2,
    year(visit_date) || '-' || lpad(month(visit_date),2,'0') as way2_padded;

giving: