Re: SELECT

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 >> >> I need help creating a mySQL query that will select the correct >> introduction message for a website I am making.  The way I have >> designed the table I can’t wrap my mind around the SELECT query that >> will deal with the day # of the month. >> >> The part of the SELECT syntax I am struggling with is when the >> introduction message is to change mid month.  The reason I am >> struggling with this is because I haven’t used ‘DATE’ for the column >> type.  The reason I didn’t use ‘DATE’ is because the same message >> will be displayed year after year, depending on the date range. >> >> 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 This might be best handled in a stored procedure, converting the values stored in the table to dates to do the comparison with in the where clause.