Re: Use Round function for date time field in SQL

On Wed, Jun 18, 2008 at 10:18 AM, saad_tariq via DBMonster.com <u43636@uwe.iiug.org> wrote: > it gives me the same error when I use Trunc however when I use Cast and > Extend it gives me an error saying syntax error... > > John Miller wrote: >>Round and Trunc are available for datetime and intervls starting >>in version 11. Prior to that you can cast and the EXTEND keyword >>to achieve the same result. Amendment: ROUND and TRUNC are available for DATETIME and DATE, but not for INTERVAL. The basic operations needed are to convert an interval into a decimal number of the appropriate time units, and then apply ROUND to that decimal value. For example: Black JL: sqlcmd -d stores SQL[2833]: select current as current, > current - datetime(2008-06-01 10:15:32) year to second as elapsed_df3, > iv_seconds(current - datetime(2008-06-01 10:15:32) year to second) as elapsed_seconds, > round(iv_seconds(current - datetime(2008-06-01 10:15:32) year to second), 1) as rounded_seconds, > iv_days(current - datetime(2008-06-01 10:15:32) year to second) as elapsed_days, > round(iv_days(current - datetime(2008-06-01 10:15:32) year to second), 1) as rounded_days > from dual; 2008-06-19 23:23:54.488|18 13:08:22.488|1602502.48800|1602502.5|18.54748250000|18.5 SQL[2834]: q; Black JL: cat iv_units.spl -- @(#)$Id: iv_units.spl,v 1.1 2008/06/19 22:11:26 jleffler Exp $ -- @(#)Stored Procedures for converting intervals to decimals CREATE PROCEDURE iv_seconds(x INTERVAL SECOND(9) TO FRACTION(5)) RETURNING DECIMAL(14,5); DEFINE s CHAR(16); LET s = x; RETURN s; END PROCEDURE; CREATE PROCEDURE iv_minutes(x INTERVAL SECOND(9) TO FRACTION(5)) RETURNING DECIMAL(15,7); RETURN iv_seconds(x) / 60; END PROCEDURE; CREATE PROCEDURE iv_hours(x INTERVAL SECOND(9) TO FRACTION(5)) RETURNING DECIMAL(15,9); RETURN iv_seconds(x) / 3600; END PROCEDURE; CREATE PROCEDURE iv_days(x INTERVAL SECOND(9) TO FRACTION(5)) RETURNING DECIMAL(16,11); RETURN iv_seconds(x) / 86400; END PROCEDURE; CREATE TEMP TABLE test_intervals ( iv INTERVAL DAY(9) TO FRACTION(5) NOT NULL ); INSERT INTO test_intervals VALUES('0 00:00:00.00000'); INSERT INTO test_intervals VALUES('+0 00:00:00.00001'); INSERT INTO test_intervals VALUES('-0 00:00:00.00001'); INSERT INTO test_intervals VALUES('+0 00:00:00.10001'); INSERT INTO test_intervals VALUES('-0 00:00:00.10001'); INSERT INTO test_intervals VALUES('+0 00:00:04.10001'); INSERT INTO test_intervals VALUES('-0 00:00:04.10001'); INSERT INTO test_intervals VALUES('+0 00:30:04.10001'); INSERT INTO test_intervals VALUES('-0 00:30:04.10001'); INSERT INTO test_intervals VALUES('+0 20:30:04.10001'); INSERT INTO test_intervals VALUES('-0 20:30:04.10001'); INSERT INTO test_intervals VALUES('+1 20:30:04.10001'); INSERT INTO test_intervals VALUES('-1 20:30:04.10001'); INSERT INTO test_intervals VALUES('+12 20:30:04.10001'); INSERT INTO test_intervals VALUES('-12 20:30:04.10001'); INSERT INTO test_intervals VALUES('+123 20:30:04.10001'); INSERT INTO test_intervals VALUES('-123 20:30:04.10001'); INSERT INTO test_intervals VALUES('+1234 20:30:04.10001'); INSERT INTO test_intervals VALUES('-1234 20:30:04.10001');-- NB: 11574 days (just over 31 years) is the maximum interval INSERT INTO test_intervals VALUES('+11234 20:30:04.10001'); INSERT INTO test_intervals VALUES('-11234 20:30:04.10001'); SELECT iv, iv_seconds(iv) as iv_seconds, iv_minutes(iv) as iv_minutes, iv_hours(iv) as iv_hours, iv_days(iv) as iv_days, round(iv_days(iv), 1) as rounded_iv_days, "X" as dummy FROM test_intervals;Black JL: This is based on some emails I exchanged back in 1999 -- the iv_seconds() is directly copied from that, and the iv_minutes(), iv_hours(), and iv_days() functions were almost immediately definable -- the only tricky bit was getting the precisions on the results exactly 'right'. I can't immediately see that conversation in the archives at groups.google.com, so it was probably a private discussion. There are ways of dealing with intervals larger than 31 years if necessary - but the shenanigans necessary are probably irrelevant to the average business application. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/ "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." NB: Please do not use this email for correspondence. I don't necessarily read it every week, even. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/ "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." NB: Please do not use this email for correspondence. I don't necessarily read it every week, even.