Excluding certain results from a query

Databases
1

Hi folks. I have a database with a date field where the dates are stored in a format like yyyy-mm-dd. I want my users to be able to search the entire date range but exclude results from certain years. Here’s the query I’ve got:

$query = "select * from table where date like \\"%$trimmed%\\" order by date";

How should I rewrite this to get the effect of

$query = "select * from dailies where date like \\"%$trimmed%\\" (EXCEPT IF DATE IS 1980-06 to 1980-12) order by date";

Thanks greatly for your help.

2

first of all, if this is an actual DATE or DATETIME column (as opposed to VARCHAR), then

  1. it isnt stored in the format you think it is, and
  2. you shouldn’t be using LIKE on it

could you please explain a bit more what you want to search for?

the “except” part of the query will look like this –


AND ( datecolumn < '1980-06-01' 
   OR datecolumn >= '1981-01-01' )
3

Thanks Rudy - the date column is listed as a “date” field and not a varchar field and the dates within it have the format of 1980-09-27 for example.

This may be a bit of an oddity - the data in this database was converted from an MS Access database into MySQL and so there might be something funny about the contents of the date column. The column type is definitely “date” though.

What I’m trying to do is create a searchable archive of comic strips where we allow people to search all the records using the date column, but we want to exclude six month’s worth from near the beginning. So we’d return no results or a friendly message when people search for anything from January 1st to June 1st of 1980, but show them strips from any other date range. It’s complicated, and we have to be able to change the date range later.

Right now the date search works, but I’m having trouble restricting access to that six-month set of data. I’ll try your suggestion.

4

That worked perfectly - thanks Rudy. You’ve helped me out so often I’m going to go buy your book. Much appreciated.

5

okay, let me know if you have any problems

and remember not to use LIKE on a DATE column

as for the format, it only ~appears~ that the stored format of a DATE column is yyyy-mm-dd, probably because the year-month-day sequence is the only way mysql will accept a date literal value

note that you can specify valid date formats not only like ‘2010-02-13’ but also like ‘2010#01#13’ and ‘2010;01;13’ and 20100113 (integer)

regardless of which valid input format you use, it is converted to an internal format for storage

internally, the format is stored in a way that you wouldn’t believe, and that you never get to see, because when you display a date, it is always converted to an output format, which, by default, is yyyy-mm-dd

6

and remember not to use LIKE on a DATE column

Regarding the above, what reasons are there for not using LIKE? It seems to work as required and has the beneficial effect of letting people search for, say, 2002-02 and getting all the data for February of 2002, or searching for just 2002 and getting a year’s worth of data.

Thanks for your help.

7

it’s inefficient (that’s the main reason) and susceptible to changes in default data display format

8

Thanks again Rudy. I just ordered your book.