Mysql Date field store in text formatted MMDDYY (01012007)

[mparker1113]

Hi,

I have saved some date fields in the above format, and it was working okay until the change of the year. Now, when I try to do a search of dates for
SELECT *WHERE ord.MMDDYYYY Between '01011999' And '01012007' order by ord.ID I have no records returned.

I have tried casting the record values to a date
---- select * where cast(ord.MMDDYY as DATE) between cast('01011999 as Date) and cast('01012007' as DATE) ---- But that doesnt work

I really kind of need to keep the database value as a text value if possible.

Any suggestions?

[NightShift58]

I'm not sure I understand how you stored the data. You mention MMDDYYYY.

How and where did you specify this format?

If that's really the format used, doing cast('01011999 as Date) won't help you because mysql's date format is YYYY-MM-DD, which will then translate the cast value to "0000-00-00" - and that's not going to get you closer to your goal.

You should consider using mysql date's format and type, as there are few penalties for doing so. When you retrieve the date information, it will be delivered as a string and when searching, you can apply string values against the content of the date fields.

[NogDog]

I agree 100% with NightShift that you should store dates in the DB as date or datetime fields, not as varchar (and you can use the date_format() MySQL function when querying the field to output it in the desired format). In the meantime, if it's not possible to change that column at this time, you could do the following:

SELECT * WHERE STR_TO_DATE(ord.MMDDYYYY, '%m%d%Y')
BETWEEN '1999-01-01' AND '2007-01-01' ORDER BY ord.ID