I know this may be stupid but none of the old cases works for me. And neither for 'CAST' method as well.
I'm trying to run the following query and I got the following error:
SELECT
P.date,
P.campaign_id,
P.ad_name,
P.impressions,
P.clicks,
P.spend,
P.NOM_ENSEMBLE,
P.CODE_ENSEMBLE_IMMO,
F.FREQUENTATION_CENTRE
FROM
`project.digital.fb_data` AS P
JOIN
`project.digital.freq_data` AS F
ON
(P.date = F.JOUR AND P.CODE_ENSEMBLE_IMMO=F.CODE_ENSEMBLE_IMMO)
Error:
Error: No matching signature for operator = for argument types: DATE, STRING. Supported signatures: ANY = ANY at [16:4]
Below is for BigQuery Standard SQL
First query is simplified query that reproduces / demonstrates your issue
#standardSQL
WITH `project.digital.fb_data` AS (
SELECT DATE '2019-02-15' `date`, 1 CODE_ENSEMBLE_IMMO
), `project.digital.freq_data` AS (
SELECT '02-15-2019' JOUR, 1 CODE_ENSEMBLE_IMMO, 'zzz' FREQUENTATION_CENTRE
)
SELECT
P.date,
P.CODE_ENSEMBLE_IMMO,
F.FREQUENTATION_CENTRE
FROM `project.digital.fb_data` AS P
JOIN `project.digital.freq_data` AS F
ON P.date = F.JOUR
AND P.CODE_ENSEMBLE_IMMO=F.CODE_ENSEMBLE_IMMO
with result
Error: No matching signature for operator = for argument types: DATE, STRING. Supported signatures: ANY = ANY at [13:4]
Below query demonstrates how to resolve above issue
#standardSQL
WITH `project.digital.fb_data` AS (
SELECT DATE '2019-02-15' `date`, 1 CODE_ENSEMBLE_IMMO
), `project.digital.freq_data` AS (
SELECT '02-15-2019' JOUR, 1 CODE_ENSEMBLE_IMMO, 'zzz' FREQUENTATION_CENTRE
)
SELECT
P.date,
P.CODE_ENSEMBLE_IMMO,
F.FREQUENTATION_CENTRE
FROM `project.digital.fb_data` AS P
JOIN `project.digital.freq_data` AS F
ON P.date = PARSE_DATE('%m-%d-%Y', F.JOUR)
AND P.CODE_ENSEMBLE_IMMO=F.CODE_ENSEMBLE_IMMO
with result
Row date CODE_ENSEMBLE_IMMO FREQUENTATION_CENTRE
1 2019-02-15 1 zzz
Note: you can have your F.JOUR
un a different format than in above example - so check PARSE_DATE function for how to deal with other supported formats