Date and Datetime in join

Database
N/A

Date and Datetime in join

Hi Team,

Do I need to cast Datetime column to Date when used in join? It seems I am getting same results with/without cast

With Cast: ON FISCAL_DAY_TO_YEAR.CALENDAR_DATE=CAST(SOL_SHIPMENTS.ACTUAL_SHIPMENT_DTM AS Date)

Without Cast: ON FISCAL_DAY_TO_YEAR.CALENDAR_DATE=SOL_SHIPMENTS.ACTUAL_SHIPMENT_DTM

I am trying to use without cast for a better join. Please let me know

Regards,

Sam

2 REPLIES
N/A

Re: Date and Datetime in join

Hi Sam,

i did that CAST for many years, too :-)

And then i investigated what was ment to be a bug (similar to your example) and finally found a place in the manual where is stated that when you compare a DATE and a TIMESTAMP the TIMESTAMP is implicity CASTed to a DATE. This behaviour is probably based on Standard SQL, but i never checked it.

The join will be no better (i.e. faster), just simpler to write.

Dieter

N/A

Re: Date and Datetime in join

Thanks for the response Dieter.  As I understand the join is no better due to the implict CAST.