The result of a datetime computation is out of ran

I need to extract all rows within a year based on a date provided by the user. ex: user input 05/31/2015 range used for extract is >= 04/30/2014 and <= 05/31/2015 I have supplied the following SQL where date >= month(user_date - 11 units month) || '/01/' || year (user_date - 11 units month) and date <= user_date I am receiving the following error when executing for some but not all dates. "The result of a datetime computation is out of range." I understand the problem but not the solution. All help is appreciated. John

Try this: where date >= ((month(user_date) - 11)::CHAR(2) || '/01/' || year (user_date - 11 units month)::CHAR(4))::DATE and date <= user_date Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, May 27, 2015 at 12:47 PM, JOHN KRUPA <jkrupa@turretsteel.com> wrote: > I need to extract all rows within a year based on a date provided by the > user. > > ex: user input 05/31/2015 > > range used for extract is >= 04/30/2014 and <= 05/31/2015 > > I have supplied the following SQL > > where date >= month(user_date - 11 units month) || '/01/' || > > year (user_date - 11 units month) and date <= user_date > > I am receiving the following error when executing for some but not all > dates. > > "The result of a datetime computation is out of range." > > I understand the problem but not the solution. > > All help is appreciated. > > John > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1140289c0cb0270517131df4