Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

Cast a partial date into a date type

  • 25 June 2019
  • 2 replies
  • 1079 views
T

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

conrad
Rank

@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)) ;;

}
T

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.

Reply


Terms of Service