SQL problem - HELP

All, I'm trying to get a date value that is 4 months from a given day. If I use: select datetime(2004-12-30) year to day + 4 units month from dummy I get 2005-04-30 However, if I try: select datetime(2004-12-31) year to day + 4 units month from dummy I get an error: 1267: The result of a datetime computation is out of range. I tried using + 120 day - but this doesn't always give you exactly 4 months. Am I out of luck here???? -Terrence

"Terrence Mu...." wrote: > >All, > >I'm trying to get a date value that is 4 months from a given day. If I >use: > >select datetime(2004-12-30) year to day + 4 units month from dummy > >I get 2005-04-30 > >However, if I try: > >select datetime(2004-12-31) year to day + 4 units month from dummy > >I get an error: >1267: The result of a datetime computation is out of range.> > >I tried using + 120 day - but this doesn't always give you exactly 4 >months. > >Am I out of luck here???? > Out of luck? No. This subject has come up many, many times. For a couple of very good threads that cover this, go to Google and search comp.databases.informix for the subject: Date calculation functions : Informix bug? or the subject: Last Date of previous month. Actually, if you just search comp.databases.informix with *all* the words 'datetime' and 'month', you'll find some very good descriptions of why 'UNITS MONTH' isn't giving you what you want, and you will find some helpful suggestions to work around your problem. Take a look at the two suggested threads first - there is a lot of information out there. These two should get you headed in the right direction. -- June Hunt _________________________________________________________________ Tax headache? MSN Money provides relief with tax tips, tools, IRS forms and more! http://moneycentral.msn.com/tax/workshop/welcome.asp

You're getting the error because 04-31 is an invalid date. (I'm sure you figured that out). If you're always going forward from the end of a month, you could go five months from the beginning of that month, then subtract 1 day.

Terrence what exactly is 4 months? What is four months from 31th Oct? Is the 31st Feb, 28th Feb, 29th Feb, 1st Mar? Until you decide how you need to return the date in these circumstances you are stuck. If all you are trying to do is get the end of the month four months away then you can do it via select datetime(2004-12-1) year to day + 5 units month - 1 units day Cheers Paul At a unix level if you tried it some Unix would error and give you the same date back, some would return the next valid date. "Terrence Mu...." wrote: > > All, > > I'm trying to get a date value that is 4 months from a given day. If I use: > > select datetime(2004-12-30) year to day + 4 units month from dummy > > I get 2005-04-30 > > However, if I try: > > select datetime(2004-12-31) year to day + 4 units month from dummy > > I get an error: > 1267: The result of a datetime computation is out of range.> > I tried using + 120 day - but this doesn't always give you exactly 4 months. > > Am I out of luck here???? > > -Terrence -- Paul Watson # Oninit Ltd # Growing old is mandatory Tel: +44 1436 672201 # Growing up is optional Fax: +44 1436 678693 # Mob: +44 7818 003457 # www.oninit.com #