Re: SELECT

On Fri, Oct 21, 2011 at 2:09 AM, Ford, Mike <M.Ford@leedsmet.ac.uk> wrote: >> -----Original Message----- >> From: tamouse mailing lists [mailto:tamouse.lists@gmail.com] >> Sent: 20 October 2011 21:37 >> >> On Tue, Oct 18, 2011 at 5:36 AM, Ford, Mike <M.Ford@leedsmet.ac.uk> >> wrote: >> >> -----Original Message----- >> >> From: Ron Piggott [mailto:ron.piggott@actsministries.org] >> >> Sent: 17 October 2011 18:38 >> >> >> >> What I am storing in the table is the start month # (1 to 12) and >> >> day # (1 to 31) and then the finishing month # (1 to 12) and the >> >> finishing day # (1 to 31) >> >> >> > >> > This is a little bit of a tricky one, as you have to consider both >> > start_month and end_month as special cases - so you need a three- >> part >> > conditional, for the start month, the end month, and the months in >> > between. Something like this: >> > >> > SELECT * FROM introduction_messages >> >  WHERE (month>start_month AND >> > month<end_month) >> >       OR (month=start_month AND day>=start_day`) >> >       OR (month=end_month AND >> > day<=end_day); >> >> This still suffers from the problem in Jim's offer -- wrap of year >> and >> wrap of month > > Look again. Month wrap *is* handled by the specific tests for start_month > and end_month. Hmm -- yes, you are right -- it does handle the month wrap problem okay. > As to year-wrap, Ron's original post said: > >> >> ... The reason I didn’t use ‘DATE’ is because the same message >> >> will be displayed year after year, depending on the date range. > > so I didn't bother about year-wrap, assuming he would include a range > with start_date of 1/1 and another with end_date of 31/12. So you are saying it can be easily worked around if there is a particular case that is supposed to wrap over the end of the year and simply include the item twice: one starting on Jan 1 and the other one ending on Dec 31. (I'm not sure if that's what you meant above.)