StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>This could probably stand some more rigorous testing, and there is certainly scope to tidy up the output (ie remove "0 months" substrings), but I think it gets you most of the way there...</p> <pre><code>CREATE PROCEDURE informix.datediff(d1 DATE, d2 DATE) RETURNING VARCHAR(255); DEFINE yrcount, mthcount, wkcount, daycount INTEGER; DEFINE dx DATE; LET mthcount = ((YEAR(d2) - YEAR(d1)) * 12) + MONTH(d2) - MONTH(d1); IF DAY(d1) <= DAY(d2) THEN LET daycount = DAY(d2) - DAY(d1); ELSE LET dx = MDY(MONTH(d1),1,YEAR(d1))+1 UNITS MONTH; LET daycount = dx - d1; -- elapsed days from last month LET daycount = daycount + DAY(d2) - 1; -- elapsed days from this month END IF; LET yrcount = mthcount / 12; LET mthcount = MOD(mthcount,12); LET wkcount = daycount / 7; LET daycount = MOD(daycount,7); RETURN d1 || " - " || d2 || ": " || yrcount || " years, " || mthcount || " months, " || wkcount || " weeks and " || daycount || " days "; END PROCEDURE; </code></pre> <p>ie:</p> <pre><code>execute procedure datediff(TODAY, "19/03/2011"); (expression) 21/06/2010 - 19/03/2011: 0 years, 9 months, 4 weeks and 0 days execute procedure datediff(TODAY, "22/03/2011"); (expression) 21/06/2010 - 22/03/2011: 0 years, 9 months, 0 weeks and 1 days execute procedure datediff("08/02/2010", "08/05/2011"); (expression) 08/02/2010 - 08/05/2011: 1 years, 3 months, 0 weeks and 0 days execute procedure datediff("31/03/2010", TODAY); (expression) 31/03/2010 - 21/06/2010: 0 years, 3 months, 3 weeks and 0 days execute procedure datediff(TODAY-3, TODAY); (expression) 18/06/2010 - 21/06/2010: 0 years, 0 months, 0 weeks and 3 days execute procedure datediff(TODAY-33, TODAY); (expression) 19/05/2010 - 21/06/2010: 0 years, 1 months, 0 weeks and 2 days </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload