Querying multiple tables in Big Query with different dateformat

Question

I'm very new to working with BigQuery and writing querys

I want to pull data from 2 different tables that each has a field with datestamps but in different formats. All fields are imported as plain text so I have to cast every field to other formats.

Table 1 is formatted '2020-10-09 00:00:00', Table 2 is formatted '2020-10-09' in the date-field This is the code I use

SELECT 
e.Region,
e.OmradeNamn,
SAFE_CAST(e.Datum as Date) as Datum,
FORMAT_DATE('%u', SAFE_CAST(e.Datum as Date)) AS Veckodag,
e.Rutt_Stilling,
...
...
...
...
r.NA_ArbPass,

FROM `leveranskvalitet` e 
  LEFT JOIN `rutterap` r 
  ON e.Rutt_Stilling = r.Rutt_Stilling
  and e.Datum = r.Datum

  Where cast(LEFT(e.Datum,10) as date) =  Cast(@DATUM as date)
  ...
  ...
  Order by e.Rutt_Stilling ASC>

From table 2 it only gets 'NULL'

If I use another table as table 1 with the dateformat '2020-10-09' I have no problem pulling data from table 2

To select date I use a cell-reference (@Datum) from another sheet

Grateful for some help with this Roger

Answer 1

assuming the data types are timestamp and date you can do the following:

with temp_ts as (
 select timestamp('2020-10-09 00:00:00') as ts
),
temp_dt as (
 select date('2020-10-09') as dt
)

select *
from temp_ts t
join temp_dt d
 on extract(date from t.ts) = d.dt;

Given your query:

SELECT 
e.Region,
e.OmradeNamn,
SAFE_CAST(e.Datum as Date) as Datum,
FORMAT_DATE('%u', SAFE_CAST(e.Datum as Date)) AS Veckodag,
e.Rutt_Stilling,
...
...
...
...
r.NA_ArbPass,

FROM `leveranskvalitet` e 
  LEFT JOIN `rutterap` r 
  ON e.Rutt_Stilling = r.Rutt_Stilling
  and date(cast(e.Datum as datetime) = cast(r.Datum as date)

  Where cast(LEFT(e.Datum,10) as date) =  Cast(@DATUM as date)
  ...
  ...
  Order by e.Rutt_Stilling ASC>