I have a sql table with multiple fields including (but not limited to the following): member_id, visit_date (in datetime format eg: 2016-01-01), visit_yr_qtr (as string, eg: 2016_Q1), purchase_item (ID number), and item_price (in US dollars).

I would like to extract these fields, but also include year_month (2016- January or 2016-01)as a new field in the output. This is what I tried to do but have not been successful:

          select member_id, visit_date, visit_yr_qtr, purchase_item, item_price, 
          EXTRACT(year_month, visit_date)
          FROM <table name>

Can somebody suggest a way to extract year_month from the visit_date column in the output?

CodePudding user response：

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:

CodePudding user response：

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')