Date Stored as Text Query Between and Higher

Question

I need to pull records from a mysql database which has everything stored as text.

The two fields i should query are stored like this format '19671011' type text and I need to select a range between and higher than.

How could I query a text field retrieving a particular month records?

select * from recordtable where birthday between () - () and anotherdate < '20081111'

Answer 1

Editing this to only return people for a certain month:

SELECT * FROM recordtable 
WHERE (MONTH(CAST(birthday AS DATE)) = 10)
AND CAST(anotherdate AS DATE) < CAST('20081111' AS DATE);

Here is a good link for SQL date calculations: http://mysql-tips.blogspot.com/2005/04/mysql-date-calculations.html

Answer 2

You can try asking MySql to cast those string values as dates (assuming that all the strings are 8 chars it should work for the comparisons you need).

Like this:

CAST('20081111' AS DATE)

Then your query might become something like this:

SELECT * FROM recordtable 
WHERE CAST(birthday AS DATE) >= CAST('20050405' AS DATE)
AND CAST(birthday AS DATE) <= CAST('20111010' AS DATE)
AND CAST(anotherdate AS DATE) < CAST('20081111' AS DATE);

Answer 3

This isn't exactly framed as a question, but let me try to answer. If the date field is always 8 characters stored YYYYMMDD, then you can just compare the fields as strings.

Answer 4

You can also use the BETWEEN operator in vbigham's solution, as follows:

You can try asking MySql to cast those string values as dates (assuming that all the strings are 8 chars it should work for the comparisons you need).

Like this:

CAST('20081111' AS DATE)

Then your query might become something like this:

SELECT * FROM recordtable 
WHERE (CAST(birthday AS DATE) BETWEEN CAST('20050405' AS DATE) 
                                      AND CAST('20111010' AS DATE))
AND CAST(anotherdate AS DATE) < CAST('20081111' AS DATE);