RE: Another Datetime/Interval question

How do I select the number of different dates for which records exist? select date (tanf), count(*) from <tablename> group by 1; Will give you the number of records for each date that exists and select distinct (date (tanf)) from <tablename>; Will give you the dates for which records exist. How do I select the number of minutes between "tanf" and "tend"? select interval(0) minute(9) to minute + (extend(tend, year to minute) - extend(tanf, year to minute)) from <tablename>; I can't take credit for the last one, there was a Technote on this posted 09/14/2005. Andrew Ford -----Original Message----- From: owner-informix-list@iiug.org [mailto:owner-informix-list@iiug.org] On Behalf Of Richard Spitz Sent: Friday, December 16, 2005 10:37 AM To: informix-list@iiug.org Subject: Another Datetime/Interval question Dear Informixers, I'm stuck with a datetime/interval problem again. Database is IDS 7.31UC8 on Linux. I'm presently working with plain old dbaccess, although ODBC might be needed in the future. I have a table whicht contains(among others) two DATETIME columns: tanf datetime year to minute, tend datetime year to minute First question: How do I select the number of different dates for which records exist? I tried SELECT COUNT(DISTINCT DATE(tanf)) FROM <tablename> but that gives me a syntax error. Second question: How do I select the number of minutes between "tanf" and "tend"? "tanf" stores the beginning of a (medical) procedure, "tend" stores the end. SELECT tend - tanf gives me the difference (interval) in days, hours and minutes (e.g. "0 02:30"), but in this case, I need "150" as the number of minutes passed. I experimented a lot with the EXTEND function and the INTERVAL qualifier, but nothing worked. Any ideas? Regards, Richard sending to informix-list