CODEWITHSUNDEEP
dateimpala
burnsa9
burnsa9

Reputation: 131

Difference in days between two dates in Impala

I am trying to find a date difference In Impala. I have tried a few options. my most recent is below

ABS(dayofyear(CAST(firstdate AS TIMESTAMP)-dayofyear(CAST(seconddate AS TIMESTAMP)

an example of data looks like:

firstDate: 2017-11-25 secondDate: 2017-11-30

The solution that I am looking for in the above scenario would be five. The provided code would give me this answer, but the trouble comes in when the first date is in december and the second one ends in January.

Firstdate: 2016-12-30 seconddate:2017-12-30

with the above code this gives me an output of 358 days,the outcome that I would want to see is 8 days. This code is only looking at what day of the year this falls on, and is not able to consider moving from year to year. I know that there is no datediff function on Impala and I've also tried using just subtraction on the two timestamps but that didn't work either. I have scoured google and have come back empty any suggestions would be greatly appreciated

Upvotes: 7

Views: 25858

Answers (1)

Ramesh
Ramesh

Reputation: 1484

SQL

SELECT DATEDIFF(TO_DATE(firstdate), TO_DATE(seconddate));

Is this what you are looking for?

Upvotes: 16

Related Questions