SQL Query Challenge

Before I figured out what the timestamp feature was I used a seperate field to store the day month and year. I believed strongly in making things difficult for myself and now I am paying the price.

Here is a query for those who like a challenge.

I want to display all entries in the database which are after a certain date and also display all entries which are before a certain date.


The table goes something like

id title day month year

where day is a 2 digit number 00 - 31
where month is a 2 digit number 01 - 12
and year is 4 digit number 1999-2006

If you think you can manage this one please help me out!


Thanks!

 

How about altering the table to add a timestamp column, then write a php script to convert all your seperate fields to the timestamp. Then you can easily do any queries, and it will be much faster..

 

Do something like

select * from table 
where 
20050101 => (year * 10000 + month * 100 + day) and
20051231 <= (year * 10000 + month * 100 + day)

Code (sql):

Than slap your self around the head with a dead fish for not using a proper date field

 

I would love to but there are scripts in place that already use the format in the database.

It's likely to be more hassle changing the database than it is working round it.


Thanks for your suggestion though.

 

What Dejavu says is the best idea, you could also do it via SQL to save you asking us a PHP question

 

Thanks dct

 

Why don't you add a timestamp field using your favourite scripting language, but leave in your legacy fields as well.

Any new pages in your site, make sure you use the timestamp field and gradually phase out your old fields as you maintain your site.

Once you are satisfied that you have purged all old code, you can remove those fields from the table.

It's all about compromise :-D

 

Like JamieC said, convert the site slowly. Take one page at the time...

Or "Rewrite and do it again =)"

 

Fantastic idea JamieC. I'm glad someone has there thinking hat on