join two file with datetime but datetime have different format and content

[Anonymous]

Hi , I have 2 file from 2 system so have the datetime with different format

as follow

is that the case I need to change the righthand side datetime format?

I also have date, time, and the_hour_of_a_day exist in the file.

if datetime not working, could I use 'Date' and 'time' join at the same time to represent the datetime?

if I can, how?

and if I have the right format

could i use both datetime and also the other floor to join the data together? (inner join will be nice)

here is the app with the data for your info

Thank you!

[YoussefBelloum]

Hi,

first thing to know is the format of your 2 date fields:

on TempNew.dateTime, it is clear that it's a Timestamp format (YYYY/MM/DD hh:mm:ss)

but what is the format on the left part (Datetime) ? YYYY/MM/DD ??:??

[zebhashmi]

Date(floor(Date#([datetime],'YYYY.MM.DD.hh.mm')),'MM/DD/YYYY') as [datetime],

[zebhashmi · Accepted solution]

file

[Digvijay_Singh]

If possible avoid join by timestamp fields, internally they are decimal numbers and chances are very high to have unmatched records which might result in cross join.

[Anonymous]

Thank you so much!

I test it, it is more accurate using 'Date'+'the hour of a day'+'floor' to join

if use datatine join i have 'the hour of a day' difference from different csv.

I did not know why

[Anonymous]

Thank you for the info. I thought maybe because of the time zoon different in the setting which I can not see