Join on date and datetime

1

how can I join two table where one column is a DATE and the second one is a DATETIME ?
I tried:

  • join open_position_hist on CAST(open_position_hist.position_date as DATE) = CAST(ts_base.valuation_date as DATE)
  • join open_position_hist on TO_DATE(open_position_hist.position_date) = TO_DATE(ts_base.valuation_date)

but both do not work. Am I missing something ?

2

Hi,

In what way does the join not work? Is it failing or not returning any data?

I ran a quick local test that worked fine for me:

SELECT a1.double_col
FROM "@dremio"."all" AS a1
JOIN "@dremio"."all2" AS a2 ON CAST(a1.timestamp_text_col AS DATE) = CAST(a2.timestamp_text_col AS DATE)
3

Hi, thank for your reply.
Sorry, my mistake, as a dremio noob I was actually executing the query with ‘preview’ and not with ‘run’. Once I checked that, everything just works fine.

4

Good to hear that it works. Run vs Preview can get confusing and it is on our list of things to improve.