CALCULATE DAYS BETWEEN TWO DATES / FIELDS IN DIFFERENT TABLES

[dplandry]

Hey guys

I have a query using data from two tables; CUSTORDR and AR_INTRASTAT.  I need to calculate the # of days between these two dates and I can't get DATEDIFF to work:

CUSTORDR - prom_delivery

AR_INTRASTAT - ship_date

Any ideas why DATEDIFF not working?  

Thanks

Dwayne

[v-sihou-msft · Accepted solution]

@dplandry

What's the relationship between those two tables? Please share sample data with table structure if possible.

Make sure one date column can map the other date column properly. Then you can use merge query to include both date columns into same table. Or you can use RELATED() to get the date column from other table.

Since the DATEIDFF() requires the end_date must be larger than start_date, I suggest you directly minus them.

Internal = 1*(CUSTORDR[prom_delivery]-AR_INTRASTAT[ship_date])

Or you can try Power Query approach, see:

https://social.technet.microsoft.com/Forums/en-US/af9dbbf7-2f13-41f3-9a51-7c5d6db1ee9d/datediff-in-p...

Regards,

[Greg_Deckler]

Would need some sample data and your formulas to tell you. An error message or what is going wrong with DATEDIFF would be cool as well.