Extract Year from date field

lookml

(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:

Best,
Andy


(cristian) #3

Thanks!
Im now using ${date_field_year} !


(Sean Higgins) #4

This also works:

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