Page 1 of 1

How to select rows that are added in a period of time?

Posted: Wed Nov 04, 2009 5:25 pm
by MicroBoy
How to select rows that are added in a period of time, example in a month? I use TEXT field for date, because the format date that I want to use is dd/MM/yyyy. And if I use a Date field the format will not be like this. I saw a lot of tutorials but they all use the other format of date.

Re: How to select rows that are added in a period of time?

Posted: Wed Nov 04, 2009 7:09 pm
by califdon
I would advise against ever using anything but the native DATE storage format when representing a date. You can format the date any way you want, using DATE() function, but you will have the ability to select by month, for example, as you want to do, quite easily, but if you start out storing the date as a text string, you will have to invent your own functions for parsing the components of the date, and you will be at the mercy of users entering invalid dates. Just study the way MySQL (assuming that's the database you're using) stores dates and times, and how you can format them with PHP DATE() function.

Re: How to select rows that are added in a period of time?

Posted: Wed Nov 04, 2009 7:21 pm
by Eric!
I totally agree with calfidon.

I know this query works with datetime fields, but I don't think it will work properly for text fields for all cases because it won't know months from days or years

Code: Select all

SELECT event FROM `table` WHERE dates>'01/05/2009' AND dates<'01/09/2009' group by event
You can test it... :| I'm quite certain it won't work.

Re: How to select rows that are added in a period of time?

Posted: Thu Nov 05, 2009 7:27 am
by MicroBoy
Yes I'm using mysql, and do you suggest to change date format in output? Or can I change the date format inside mysql?

Re: How to select rows that are added in a period of time?

Posted: Thu Nov 05, 2009 1:25 pm
by califdon
MicroBoy wrote:Yes I'm using mysql, and do you suggest to change date format in output? Or can I change the date format inside mysql?
Read the last sentence of my previous post.

STORE all dates as DATE data types in the database. Use PHP to display dates in any format you want, wherever you want, whenever you want. If you already have data in your database, be very careful about changing data types! Don't just change a VARCHAR to a DATE or you will risk losing your data! Create a new field of DATE data type, then use SQL to populate the new field with the proper data from the old field. Then you can check whether the translation gives you valid results. When you are sure that it's correct, you can delete the old field.

Re: How to select rows that are added in a period of time?

Posted: Thu Nov 05, 2009 1:39 pm
by MicroBoy
Thnx a lot for help:D, finally I made it.