MySQL - Less Than OR Between?

Databases
1

Hi Guys,

I need some help please, i have a field called Date Of Birth which I’ve placed as Text as the data is the following:

26/03/1990

How do i go about doing a query to find people aged 21 and over?

I’ve tried the following but keep getting MySQL Errors:

SELECT * FROM noAOL WHERE 'Date Of Birth' BETWEEN %1990% AND %1800%

SELECT * FROM noAOL WHERE 'Date Of Birth' < '%1990%'

Both give me errors, any help would be great please.

Thank you.

2

Make DoB a date column.

3

Thanks for your reply, I’ve done that and the SQL Querys still dont work :frowning:

Any help would be great.

Thanks

4

I’ve tried the following which works but is returning 0 results.

SELECT * 
FROM noAOL
WHERE  'Date Of Birth'
BETWEEN  '01/01/1800'
AND  '01/01/1990';

Any help would be great.

Thanks

5

there are two major problems here

first of all, ‘Date Of Birth’ is a string, and you probably wanted it to be a column name

see, right away you ran into trouble because your column name has spaces in it, and you didn’t escape the name properly

so my advice is don’t get fancy, rename your column date_of_birth (no spaces) and you won’t have to worry ever again about properly escaping it

second thing wrong is that if it is an actual DATE column, then the values you’re passing to compare it to in your BETWEEN range are not valid mysql dates

valid mysql dates are always given in year-month-day sequence, e.g. ‘2009/09/09’, ‘2010-10-10’, ‘2011$11$11’ (you will notice that the separator can be any character)

6

Try


SELECT * 
FROM noAOL
WHERE  'Date Of Birth' < '1990-01-01'
7

guido!! :eek: :eek: :x

8

What?
I noticed the spaces in the column name, and the normal quotes around them. But the OP said that query “work’s” (no errors), so I assumed he really uses backticks and just used normal quotes to post it here.

We would have gotten there eventually :smiley:

9

Hi,

The problem i have is all the data is in UK format, we display our dates as DD/MM/YYYY. So how would i get around that?

guido i already tried that and its showing 0 results.

Thank for your help so far guys!

Thank you.

10

that’s fine

you would dictate the display format in your front end application language while obtaining the date as a date

a less attractive option is to use the the DATE_FORMAT mysql function to extract the date and arrange it into a custom string to pass back to the front end application language as a string

but to enter information into the database, and for specifying values e.g. in a WHERE clause range test, you must use year-month-day sequence in your sql

11

Yeah, r937 already explained why. It’s because ‘Date of Birth’ is a string, not a column name, so the query confronts two strings, and ‘Date of Birth’ is obviously never the same as ‘1990-01-01’, so it will return nothing.