Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2011
    Posts
    82

    Unanswered: Does informix support the Datediff function?

    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

  2. #2
    Join Date
    Nov 2010
    Posts
    16
    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

  3. #3
    Join Date
    Dec 2011
    Posts
    82
    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

  4. #4
    Join Date
    Nov 2010
    Posts
    16
    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

  5. #5
    Join Date
    Dec 2011
    Posts
    82
    Hello

    Thank you so much, this works a treat for me

    Thanks
    Helen

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •