Date_diff function for days

[Former Member]

I should calculate the number of days between two dates. To do that I used Date_diff function, however noticed this function returns one day less than it should return. For example :

Date_Diff ('2011.11.16', '2011.11.17') = 0

It should be 1, I can just add 1 to the result to work around it, but would like to see why DS behaves like this.

Have you faced with this issue? Any idea why?

[former_member467822 · Accepted solution]

I just tested the function and it works fine, however you need to include the format string parameter so the function knows you want the different in days.

$TextString = Date_Diff ('2011.11.16', '2011.11.17','D') ;

print($TextString);

The result will be 1.

[former_member467822 · Accepted solution]

Tootia, when you have a chance please verify that this fixes your problem and then please mark this as the answer so we can close the issue.

[Former Member · Accepted solution]

Thanks so much for your replys. I tested the function with fixed dates (like what you had done) and the result was correct. However when I use it in the real Data Flow that gets the Start and End dates from the table, it returns 1 less day!

The number of records in the table is just 11 records and I have defined start and end dates as Date type and :

Start Date: to_Date(REFERRAL_DIM.REFERRAL_DATE_TIME, 'YYYY.MM.DD')

End Date: to_Date(REFERRAL_DIM.OUTCOME_END_DATE_TIME, 'YYYY.MM.DD')

Then DayBetween:

date_diff(QRY.Start_Date, QRY.End_Date, 'D')

Again when the start date is '2010.11.16' and End date is '2010.11.17', the result is 0!

[former_member467822 · Accepted solution]

It sounds like this might be a problem with the date conversion instead of the date_diff function.

I would recommend running the to_date function in one query transform and then the date_diff in the next query transform.

Then run the job in debug mode to verify that the to_date function is properly converting your strings to dates. Also make sure the output column data type for the start and end dates are actually set to date.

[Former Member · Accepted solution]

Yes you were right. It was a problem in conversion to date. I was ignoring warnings about it. The conversion to date type should have been done as:

TO_DATE(TO_CHAR(REFERRAL_DIM.REFERRAL_DATE_TIME,'YYYY.MM.DD'), 'YYYY.MM.DD')

Thanks for your help.

[Former Member]

The formula works perfectly fine... you can do the debugging on the generated program where you can see the difference is getting calculated..