how to join date columns defined as string data type and are in different formats in bigquery?

Question

I have two tables Master and Platforms. I am trying to Join the two tables based on Date columns. But the Date column in Master table is in the format YYYY-MM-DD and the data type is String, the Date Column in Platforms table is in the format MM/DD/YY and the data type is String.

The join condition I am trying is Master.Date BETWEEN Platforms.StartDate and Platforms.EndDate

I have tried to parse and convert the columns to date but it doesn't yield any records.

SAFE.PARSE_DATE("%Y-%m-%d", Master.Date) BETWEEN 
SAFE.PARSE_DATE("%m/%d/%Y",Platforms.StartDate) AND SAFE.PARSE_DATE("%m/%d/%Y",Platforms.EndDate)

What should I add to compare these columns?

Query:

select * from Master, Platforms
where
SAFE.PARSE_DATE("%Y-%m-%d", Master.Date) BETWEEN 
SAFE.PARSE_DATE("%m/%d/%Y",Platforms.StartDate) AND SAFE.PARSE_DATE("%m/%d/%Y",Platforms.EndDate)


Master Table Ex:

Name    Date        ID
Alex    2019-01-25  1
David   2019-02-25  2
Seth    2019-03-25  3
Peter   2019-04-25  4
Taylor  2019-05-25  5

Platform Table Ex:

Type    StartDate   EndDate
Abc     01/05/19    01/31/19
Def     02/25/19    03/31/19
Ghi     05/01/19    05/24/19
klm     05/01/19    05/25/19

Expected O/P:

Name    Date        ID  Type    StartDate   EndDate
Alex    2019-01-25  1   Abc     01/05/19    01/31/19
David   2019-02-25  2   Def     02/25/19    03/31/19
Taylor  2019-05-25  5   klm     05/01/19    05/25/19

Gordon Linoff · Accepted Answer · 2019-11-12 19:32:04Z

1

Try '%y' (lower-case):

where SAFE.PARSE_DATE("%Y-%m-%d", Master.Date) BETWEEN 
          SAFE.PARSE_DATE('%m/%d/%y', Platforms.StartDate) AND 
          SAFE.PARSE_DATE('%m/%d/%y', Platforms.EndDate)

'%Y' in a format specification treats the year as a four-digit year. So, "19" --> "0019". Presumably, you want "2019", which is why '%y' is appropriate.

answered Nov 12, 2019 at 19:32

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to join date columns defined as string data type and are in different formats in bigquery?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related