Issues with date format on hscic.raw_prescribing_normalised

[richiecroker]

I attempted to write a new measure definition using hscic.raw_prescribing_normalised as the numerator, and list_size as the denominator, and it threw the following error:

No matching signature for operator = for argument types: TIMESTAMP, DATE. Supported signature: ANY = ANY at [86:15], with line 86 being AND practices_with_months.month = num.month

On investigation I found a stackoverflow discussion on the same issue.

Looking at how we create the SQL for the view, we have this line:
PARSE_DATETIME("%F", REGEXP_REPLACE(raw_prescribing._FILE_NAME, "^.+/(20\\d\\d)_(\\d\\d)/[^/]+$", "\\1-\\2-01")) AS month, which creates a DATETIME field type.

Other views, such as hscic.normalised_prescribinghave a TIMESTAMP field type.

As a temporary workaround, I have created a new view hscic.raw_prescribing_normalised_date which replaced the line above with PARSE_TIMESTAMP("%F", REGEXP_REPLACE(raw_prescribing._FILE_NAME, "^.+/(20\\d\\d)_(\\d\\d)/[^/]+$", "\\1-\\2-01")) AS month, which seems to work fine.

@evansd @inglesp should we replace the line in hscic.raw_prescribing_normalised with the PARSE_TIMESTAMP version, or is that likely to break anything?

[evansd]

I think we should update the existing view to use PARSE_TIMESTAMP as you suggest. As you say, the other views use this type and I can see quite a few places in the code where we explicitly cast dates to TIMESTAMP so we're obviously expecting that. My guess is that we used PARSE_DATETIME inadvertently when defining this view and then never noticed because we never attempted to a join on that column until you created that measure.