Cast a partial date into a date type

  • 25 June 2019
  • 2 replies

One of the data fields we pull is a string that in the ‘YYYY-MM’ format. I want to cast this as a Date type but TO_DATE or CAST does not like that the string is a partial date and not a complete one.

Is there a way to get around this? This field remaining a string is beginning to cause issues in a lot of our analysis.

2 replies

@TJT1 Can you do something like the following to solve this?

dimension: start_of_month_as_date {
type: date
sql: CAST(CONCAT(<month_string_column>, "-01"), DATE)) ;;

This ended up working but I did discover one peculiarity. You have to use a date other than the 1st of the month otherwise Looker offsets the month range by 1. IE you have Mar/Apr/May becomes Feb/Mar/Apr…

I have no idea why it does this but changing the concat to “-15” solves that issue.