How to select rows that are added in a period of time?
Moderator: General Moderators
How to select rows that are added in a period of time?
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?
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?
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 yearsYou can test it...
I'm quite certain it won't work.
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 eventRe: How to select rows that are added in a period of time?
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?
Read the last sentence of my previous post.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?
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?
Thnx a lot for help:D, finally I made it.