Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to find the difference between two dates. My original data is in numeric form start date "20181302" and end date "20180702". These are in "YYYYMMDD" format. In the load script I have changed the dates to date format. When I subtract the two dates if they have different years then it works correctly. However, if the years are the same then only the days subtract. With my example, I get -11 when the answer should be 139.
Here is my formula:
fabs(interval(floor(date(date#([End Date],'yyyymmdd'),'m/d/yyyy'))-floor(date(date#([Start Date],'yyyymmdd'),'m/d/yyyy')),'d'))
I have tried multiple other methods and still get the same answer. Please help.
The conversion-pattern from date#() isn't correct because the chars should be in upper-case and the order of months and days is wrong for your input-data. Therefore try it with:
interval(date#([End Date],'YYYYDDMM')-date#([Start Date],'YYYYDDMM'),'D')
The conversion-pattern from date#() isn't correct because the chars should be in upper-case and the order of months and days is wrong for your input-data. Therefore try it with:
interval(date#([End Date],'YYYYDDMM')-date#([Start Date],'YYYYDDMM'),'D')