UK Date Format - Between - Help!

micky007

Hi Guys,

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

26/03/1990

DD/MM/YYYY

But when i do Queries it keeps returning 0 results.

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

Any help would be great please.

Thank you.

sneakyimp

I'm wondering first of all if that SQL is valid syntax or whether it returns an error. Are you getting an error?

Secondly, you should probably tell us what kind of fiedl Date Of Birth is. Try reading the documentation on the field type (probably a field of type Date). My suspicion is that you might want to try formatting your dates in the query as 'YYYY-MM-DD' rather than 'DD/MM/YYYY'. Also, if your field 'Date of Birth' is of type text, then you are likely to get results that are totally wrong.

micky007

sneakyimp;10989083 wrote:
I'm wondering first of all if that SQL is valid syntax or whether it returns an error. Are you getting an error?

Secondly, you should probably tell us what kind of fiedl Date Of Birth is. Try reading the documentation on the field type (probably a field of type Date). My suspicion is that you might want to try formatting your dates in the query as 'YYYY-MM-DD' rather than 'DD/MM/YYYY'. Also, if your field 'Date of Birth' is of type text, then you are likely to get results that are totally wrong.

Its already set as Date. How do i go about formatting the data to YYYY-MM-DD as theres over 1 million records that will need this done?

Thanks

sneakyimp

I didn't say change the records, I said change the query:

SELECT * FROM `noAOL` WHERE `Date Of Birth` BETWEEN '1800-01-01' AND '1990-01-01'

micky007

MySQL returned an empty result set (i.e. zero rows). ( Query took 1.7850 sec )

🙁 Thanks for trying to help me.

If you can think of anything else please let me know.

Thank you.

sneakyimp

Are you sure you have any records in that date range? Also, did you use the query I pasted? You'll note that it uses backticks instead of single quotes around Date of Birth. Better yet, use this query:

SELECT t.* FROM `noAOL` t WHERE t.`Date Of Birth` BETWEEN '1800-01-01' AND '1990-01-01'

micky007

sneakyimp;10989090 wrote:
Are you sure you have any records in that date range? Also, did you use the query I pasted? You'll note that it uses backticks instead of single quotes around Date of Birth. Better yet, use this query:
SELECT t.* FROM `noAOL` t WHERE t.`Date Of Birth` BETWEEN '1800-01-01' AND '1990-01-01'

Yea i used the SQL to told me to and have just used the one you have just posted. Same thing:

MySQL returned an empty result set (i.e. zero rows). ( Query took 1.7554 sec )

Yes there are loads of records that are between those values. Theres 1.7 million records.

micky007

If i use the below SQL it displays all 1,688,893 records.

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

sneakyimp

Not sure what to tell you. If you read the documentation like I suggested, you'll get a lot more detail on how mysql deals with dates. Of course, you never said what dbms you are using so I'm assuming MySQL.

Generally speaking, you'll see that the preferred date formats put the year first, then the month, then the day. YYYYMMDD rather than the other way around. I can't help but wonder what your dates look like in your database -- perhaps they were entered incorrectly?

Also, you haven't bothered to post the schema of your table. that might also tell us something.

bradgrafelman

micky007;10989075 wrote:
i have a field called Date Of Birth which I've placed as Date as the data is the following:

26/03/1990

DD/MM/YYYY

The column Date Of Birth can't be a DATE column if it contains values like '01/01/1800'. DATE columns can only contain dates in the format 'YYYY-MM-DD'.