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

user_9194965 0
Viewed 350

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?

snowflake-cloud-data-platform extract datetime sql
2 Answers

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

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:

VISIT_DATE WAY_1 WAY2 WAY2_PADDED
2016-01-01 2016-01 2016-1 2016-01
user_439920
Related