Extract Year from date field


(cristian) #1

Hi All!
I have a simple dimension with a date field. I want to create another column with the year from this date field but it doesn’t work so I’m obviously doing something wrong. Any suggestions?

dimension: date_field{
type: date
sql:${TABLE}.date ;;

dimension: period {
type: number
sql: EXTRACT(YEARS from ${date_field});;

(Andrew Powell) #2

Hi Cristian,

You’re better off using dimension_group:

dimension_group: date_field {
   type: time
   timeframes: [year, month, month_num, month_name, date]
   sql: ${TABLE}.date ;;

You can then refer to this dimension and append _<timeframe> to get the relevant part of the date, i.e. ${date_field_date} gives you the full date ${date_field_year} gives you the year, ${date_field_month_name} gives you the name of the month, etc. Read more about dimension_group here:


(cristian) #3

Im now using ${date_field_year} !

(Sean Higgins) #4

This also works:

dimension: year { 
type: date_year
sql: ${TABLE}.created_at;;