Hello

Does Informix support the DateDiff function?

If so, could someone kindly write a simple syntex I.e. use d or dd or day for something like

DATEDIFF(d, 01/10/2011, 15/10/2011) date

I have been surfing the internet for ages now to find something that I can use to find the difference in dates for an informix database

Thanks
Helen

SELECT
MDY(10,15,2011) - MDY(10,1,2011)
FROM sysmaster:sysdual

sysdual is used as an example and exists only on 11+ (you don't state your Informix version).
MDY was used just for clarity. I don't like being dependent on environment settings like $DBDATE

If you want to use timestamps (instead of dates) or want the result in something else other than days it can be a bit more complex...

Regards

Thank you so much for your response

This works on my database

Could I kindly ask another question

How would I pass a field into the brackets, so I can use the MDY function like follows:

MDY(a.movedate) - MDY(b.movedate)

Is this a possibility?

Many Thanks
Helen

I believe I didn't explain myself correctly.
I used the MDY function to avoid as explicit date expression that depends on the DBDATE variable (which defines the format).

If you have a column of type DATE this problem does not happen.

In any case, if you really wanted to do something like that, you need to consider the fact that MDY takes 3 parameters. Month, Day and Year.
As such you could do something like:

MDY (MONTH(date_column), DAY(date_column), YEAR(date_column))

But assuming date_column is a date/datetime datatype this would be useless.

Regards

Hello

Thank you so much, this works a treat for me

Thanks
Helen