Date_diff function for days
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?
Joe Mismas replied
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.