MySQL Date Range select problem

Rains

I’m trying to shoehorn some functionality onto an application that is used for keeping track of time spent on projects. I’m also not the original developer, so I don’t know why something’s were done the way they are. This application uses 3 columns in the MySQL database (Month, Day, Year) instead of the usual timestamp or datetime. I’m having a problem doing a SQL query that pulls up records between two dates that are selected from an HTML form. How should I write my SQL query if I wanted to select everything from January 1st 2006 (Month =1 Day = 1 Year = 2006) to March 30th 2006 (Month – 3 Day =30 Year = 2006)?

Sxooter

You really should be storing those as timestamps or datetime types. Those allow you do this super easy, like:

select * from table where datefield between '2006-01-01 12:00:00' and '2006-01-01 14:30:00'

You're just making more work for yourself.

Rains

That's exactly what I would have done if I was the orginal developer. This application for the most part has been is use for several years, so I've got something like 24,000 records in one table alone. Unless there is an easy way to covert those records to timestamps, I'm still stuck.

Sxooter

You could add a field to it and update it:

alter table aaa add column ts timestamp;
update table aaa set ts=cast(concat(year,'-',month,'-',date) as timestamp);

or something like that.

If you're running mysql with the ansi switch on, you'd do it this way:

update table aaa set ts=cast(year||'-'||month||'-'||date) as timestamp);