Days between function not working

[rksingh]

Hi,

I have 2 dates and have to find the difference between these 2 dates. When use _days_between ([ReturnCompDate],[ReturnD]), it returns error. When I simply subtract (ReturnCompDate - ReturnD), I get the output as "5 Days". Now I need only 5, so I am using substr(), but it also throws error.

Any idea how to extract the numeric part from the result output(5 Days)?

[raj_aries]

what is the error that you are getting while running it with _days_between, also check if you can use vendor specific functions based on your DB. Ideally I dont see a reason why its failing if you use _days_between

[Lynn]

You get "5 days" from doing subtraction because the result returned is an interval data type, not a string or an integer.

As raj indicated, you should provide the error message to get more useful responses. I suspect you may have incorrect data types in those two query items you are using with the days between function. Just because it looks like a date doesn't mean it is a date data type. Are those elements something you compute within your query or are they coming from your package?

[RubenvdLinden]

I had the same problem with _days_between on Oracle 9i. Newer versions of Oracle (same data!) work fine.

I solved the problem with a cast: cast(date2 - date1; smallint)
This will convert '5 days' to just 5.

[yogeswari]

Hi,

I am not sure of whether you are using Date datatype only.
Because i tried by using date datatype and _days_between function.  I am getting correct results.

Kindly check and tell.

Thanks,
Yogeswari.