year function

Posted in 2008

Summary

Asked how to add 16 years to a DATE column, the poster was told the answer is Informix's interval syntax: SELECT dob + 16 UNITS YEAR (or UPDATE ... SET dob = dob + 16 UNITS YEAR). Others warned this fails for 29 February birthdays when the target year isn't a leap year (e.g. 1884-02-29 + 16 = 1900-02-29, which doesn't exist). Workarounds posted include a CASE expression special-casing Feb 29, an expression using MDY(month,1,year)+units, and Jonathan Leffler's SPL function add_n_years() with leap-year logic and test cases.

Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.

Topics: SQL Development & Query Writing

SUNITA RAINA — Wed, 05 Mar 2008 17:15:48 — source: IIUG Forums & Mailing Lists

Hi, I am looking for a function that will allow me to add 16 years to dob field. My select statement is like this: select dob + 16 from tbl1 I want 16 years added to dob field. dob is date field with month/date/year. I want it add 16 years in same format. Can you please respond asap if you know that answer. I checked internet but nothing is helping. Thank you so much, Sunita Raina

↪ replying to SUNITA RAINA

Everett Mills — Wed, 05 Mar 2008 17:21:55 — source: IIUG Forums & Mailing Lists

UPDATE my_table SET dob = dob + 16 UNITS YEAR --EEM > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > SUNITA RAINA > Sent: Wednesday, March 05, 2008 11:16 AM > To: ids@iiug.org > Subject: year function [11496] > > Hi, > > I am looking for a function that will allow me to add 16 years to dob > field. > My select statement is like this: > select dob + 16 from tbl1 > I want 16 years added to dob field. dob is date field with > month/date/year. I > want it add 16 years in same format. > Can you please respond asap if you know that answer. > I checked internet but nothing is helping. > > Thank you so much, > Sunita Raina > > > ************************************************************************ ** > ***** > Forum Note: Use "Reply" to post a response in the discussion forum. > > See you at the IIUG Informix 2008 Conference > The Power Conference for Informix Professionals > April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas > http://www.iiug.org/conf > Registration Now Open!!

↪ replying to SUNITA RAINA

Celso Cabral Coimbra — Wed, 05 Mar 2008 18:07:22 — source: IIUG Forums & Mailing Lists

Hi, you can use the sintax select dob + 16 units year from tbl1. Celso ccoimbra@cleartech.com.br <mailto:ccoimbra@cleartech.com.br> Fone: (11) 3576 4509 - Fax: (11) 3576 4515 www.cleartech.com.br <http://www.cleartech.com.br/>; -----Mensagem original----- De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]Em nome de SUNITA RAINA Enviada em: quarta-feira, 5 de março de 2008 14:16 Para: ids@iiug.org Assunto: year function [11496] Hi, I am looking for a function that will allow me to add 16 years to dob field. My select statement is like this: select dob + 16 from tbl1 I want 16 years added to dob field. dob is date field with month/date/year. I want it add 16 years in same format. Can you please respond asap if you know that answer. I checked internet but nothing is helping. Thank you so much, Sunita Raina ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!!

↪ replying to Everett Mills

Alan Denney — Wed, 05 Mar 2008 22:28:13 — source: IIUG Forums & Mailing Lists

On Wed, 5 Mar 2008 12:21:56 -0500 (EST), "Everett Mills" <eemills@nationalbeef.com> wrote: >UPDATE my_table SET dob = dob + 16 UNITS YEAR> >--EEM Note that this will fail when dob = 2/29/1884, 2/29/2084, 2/29/2184, etc. Don't say you weren't warned. [-;

↪ replying to SUNITA RAINA

GARY GU — Thu, 06 Mar 2008 03:36:26 — source: IIUG Forums & Mailing Lists

try this: case when month(dob) = 2 and day(dob) = 29 then dob+1 + 16 units year else dob + 16 units year end

↪ replying to SUNITA RAINA

Jonathan Leffler — Sat, 08 Mar 2008 06:59:47 — source: IIUG Forums & Mailing Lists

On Wed, Mar 5, 2008 at 9:15 AM, SUNITA RAINA <sraina@idoc.idaho.gov> wrote: > I am looking for a function that will allow me to add 16 years to dob field. > My select statement is like this: > select dob + 16 from tbl1 > I want 16 years added to dob field. dob is date field with month/date/year. I > want it add 16 years in same format. As Gary, Everett and Celso commented, the basic answer is to 16 UNITS YEAR to the value. As Gary alluded, this works pretty well except for leap years and the 29th of February. In fact, with 16 years being added, the only times it would cause problem is when the DOB (date of birth) field is actually 29th of February in a year xx84 where xx+1 modulo 4 is not zero (so 1884-02-29 would be problematic because 1900-02-29 did not exist (MS Excel notwithstanding!); similarly, with 1784-02-29 or 2084-02-29). Similar rules would apply to any age that is a multiple of 4 years. If the number of years is not a multiple of 4, then 29th February as the DOB causes more troubles. Here's a stored procedure that works... -- @(#)$Id: addnyears.spl,v 1.1 2008/03/08 06:56:58 jleffler Exp $ -- @(#) -- @(#)Add N years to a DATE safely CREATE FUNCTION add_n_years(dt DATE, n INTEGER) RETURNING DATE; DEFINE yy INTEGER; DEFINE mm INTEGER; DEFINE dd INTEGER; DEFINE rv DATE; LET mm = MONTH(dt); LET dd = DAY(dt); LET yy = YEAR(dt) + n; IF yy < 0 OR yy > 9999 THEN RAISE EXCEPTION -1204; -- Invalid year in date ELIF mm != 2 OR dd != 29 THEN -- Not 29th February! LET rv = MDY(mm, dd, yy); ELIF MOD(yy, 4) != 0 OR (MOD(yy, 100) == 0 AND MOD(yy, 400) != 0) THEN -- Not a leap year! LET rv = MDY(2, 28, yy); -- Or use 1st March ELSE LET rv = MDY(2, 29, yy); END IF; RETURN rv; END FUNCTION; CREATE PROCEDURE check_add_n_years(dt DATE, n INTEGER, rq DATE) RETURNING DATE, INTEGER, DATE; DEFINE rv DATE; LET rv = add_n_years(dt, n); IF rv != rq THEN RAISE EXCEPTION -746, "Incorrect result"; END IF; RETURN dt, n, rq; END PROCEDURE; EXECUTE FUNCTION check_add_n_years(MDY(1, 1,2000), +16, MDY(01,01,2016)); EXECUTE FUNCTION check_add_n_years(MDY(1, 1,2000), -16, MDY(01,01,1984)); EXECUTE FUNCTION check_add_n_years(MDY(2,28,2000), -16, MDY(02,28,1984)); EXECUTE FUNCTION check_add_n_years(MDY(2,28,2000), +16, MDY(02,28,2016)); EXECUTE FUNCTION check_add_n_years(MDY(2,29,2000), -16, MDY(02,29,1984)); EXECUTE FUNCTION check_add_n_years(MDY(2,29,2000), +16, MDY(02,29,2016)); EXECUTE FUNCTION check_add_n_years(MDY(2,29,1984), +16, MDY(02,29,2000)); EXECUTE FUNCTION check_add_n_years(MDY(2,29,2016), -16, MDY(02,29,2000)); EXECUTE FUNCTION check_add_n_years(MDY(2,29,1884), +16, MDY(02,28,1900)); EXECUTE FUNCTION check_add_n_years(MDY(2,29,1916), -16, MDY(02,28,1900)); EXECUTE FUNCTION check_add_n_years(MDY(2,29,2000), -15, MDY(02,28,1985)); EXECUTE FUNCTION check_add_n_years(MDY(2,29,2000), +15, MDY(02,28,2015)); DROP PROCEDURE check_add_n_years; -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2007.0914 -- http://dbi.perl.org/ NB: Please do not use this email for correspondence. I don't necessarily read it every week, even.

↪ replying to Jonathan Leffler

GARY GU — Mon, 10 Mar 2008 01:30:14 — source: IIUG Forums & Mailing Lists

Thanks for Jonathan's clearly explanation, didn't think it is so tricky. After some test would change my pre-sql to the below: case when month(dob) = 2 and day(dob) = 29 then mdy(3,1,year(dob)+16)-1 + 0 units year else dob+1 +16 units year -1 units day end idea is to use March 1st minus one day to get the last day of Feb for the year. "+ 0 units year" is to keep the normal output format, eg. 1900-02-28 just using "else dob +16 units year" will be failed if dob is '02/29/1884'. Think can work out if want to return '02/29/1884'+16 year as '03/01/1990'. May still not perfect if someone can give a full test. Thanks, gary_gu@engin.com.au

↪ replying to Jonathan Leffler

Long Nguyen — Wed, 19 Mar 2008 00:45:50 — source: IIUG Forums & Mailing Lists

I happen to see this a bit late but would like to contribute. How about this: select mdy(month(dob),1, year(dob)) + 16 units year + (day(dob)-1) units day from tbl1 Result: if dob is 1884-02-29 then it will be 1900-03-01. Cheers, Long Huy Nguyen MIS(Analyst Programmer) Ruralco Limited P.O.Box 515 Wentworthville NSW 2145 (Ph) 02 9688 8528 (Fax) 02 9896 7763 lnguyen@ruralco.com.au ================================================================================ ======= "Jonathan Leffler" To: ids@iiug.org <jleffler.iiug@gm cc: ail.com> Subject: Re: year function [11539] Sent by: ids-bounces@iiug. org 08/03/2008 04:59 PM Please respond to ids On Wed, Mar 5, 2008 at 9:15 AM, SUNITA RAINA <sraina@idoc.idaho.gov> wrote: > I am looking for a function that will allow me to add 16 years to dob field. > My select statement is like this: > select dob + 16 from tbl1 > I want 16 years added to dob field. dob is date field with month/date/year. I > want it add 16 years in same format. As Gary, Everett and Celso commented, the basic answer is to 16 UNITS YEAR to the value. As Gary alluded, this works pretty well except for leap years and the 29th of February. In fact, with 16 years being added, the only times it would cause problem is when the DOB (date of birth) field is actually 29th of February in a year xx84 where xx+1 modulo 4 is not zero (so 1884-02-29 would be problematic because 1900-02-29 did not exist (MS Excel notwithstanding!); similarly, with 1784-02-29 or 2084-02-29). Similar rules would apply to any age that is a multiple of 4 years. If the number of years is not a multiple of 4, then 29th February as the DOB causes more troubles. Here's a stored procedure that works... -- @(#)$Id: addnyears.spl,v 1.1 2008/03/08 06:56:58 jleffler Exp $ -- @(#) -- @(#)Add N years to a DATE safely CREATE FUNCTION add_n_years(dt DATE, n INTEGER) RETURNING DATE; DEFINE yy INTEGER; DEFINE mm INTEGER; DEFINE dd INTEGER; DEFINE rv DATE; LET mm = MONTH(dt); LET dd = DAY(dt); LET yy = YEAR(dt) + n; IF yy < 0 OR yy > 9999 THEN RAISE EXCEPTION -1204; -- Invalid year in date ELIF mm != 2 OR dd != 29 THEN -- Not 29th February! LET rv = MDY(mm, dd, yy); ELIF MOD(yy, 4) != 0 OR (MOD(yy, 100) == 0 AND MOD(yy, 400) != 0) THEN -- Not a leap year! LET rv = MDY(2, 28, yy); -- Or use 1st March ELSE LET rv = MDY(2, 29, yy); END IF; RETURN rv; END FUNCTION; CREATE PROCEDURE check_add_n_years(dt DATE, n INTEGER, rq DATE) RETURNING DATE, INTEGER, DATE; DEFINE rv DATE; LET rv = add_n_years(dt, n); IF rv != rq THEN RAISE EXCEPTION -746, "Incorrect result"; END IF; RETURN dt, n, rq; END PROCEDURE; EXECUTE FUNCTION check_add_n_years(MDY(1, 1,2000), +16, MDY(01,01,2016)); EXECUTE FUNCTION check_add_n_years(MDY(1, 1,2000), -16, MDY(01,01,1984)); EXECUTE FUNCTION check_add_n_years(MDY(2,28,2000), -16, MDY(02,28,1984)); EXECUTE FUNCTION check_add_n_years(MDY(2,28,2000), +16, MDY(02,28,2016)); EXECUTE FUNCTION check_add_n_years(MDY(2,29,2000), -16, MDY(02,29,1984)); EXECUTE FUNCTION check_add_n_years(MDY(2,29,2000), +16, MDY(02,29,2016)); EXECUTE FUNCTION check_add_n_years(MDY(2,29,1984), +16, MDY(02,29,2000)); EXECUTE FUNCTION check_add_n_years(MDY(2,29,2016), -16, MDY(02,29,2000)); EXECUTE FUNCTION check_add_n_years(MDY(2,29,1884), +16, MDY(02,28,1900)); EXECUTE FUNCTION check_add_n_years(MDY(2,29,1916), -16, MDY(02,28,1900)); EXECUTE FUNCTION check_add_n_years(MDY(2,29,2000), -15, MDY(02,28,1985)); EXECUTE FUNCTION check_add_n_years(MDY(2,29,2000), +15, MDY(02,28,2015)); DROP PROCEDURE check_add_n_years; -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2007.0914 -- http://dbi.perl.org/ NB: Please do not use this email for correspondence. I don't necessarily read it every week, even. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!! Disclaimer: This correspondence is for the named person's use only. It may contain confidential or legally privileged information or both. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this correspondence in error, please immediately delete it together with any attachments from your system and notify the sender. You must not disclose, copy or rely on any part of this correspondence if you are not the intended recipient. Any opinions expressed in this message are those of the individual sender, except where the sender expressly, and with authority, states them to be the opinions of Ruralco Holdings Limited or any of its subsidiaries (collectively "Ruralco"). Although all care has been taken to screen this communication for viruses, neither the sender nor Ruralco warrants that any communication via the Internet is free of errors, viruses, interception or interference. Information is distributed without warranties of any kind.