Page 1 of 1
searching by date
Posted: Sun May 15, 2005 7:58 pm
by bluesman333
I have a table with date formated like 1/1/2005.
Rather than have to convert it to mysql date 2005/01/01, I'd like to figure out how I can keep this format and still do my search.
I'm searching in the table for rows that have a date after a given date.
Can this be done with the current format of 1/1/2005?
Posted: Sun May 15, 2005 8:11 pm
by SBro
If it's formatted as you described and defined as a varchar or textfield etc. then the only way I can see you can do it is to select all the records, then filter the results using some php code. I don't think it would be possible with the way you described using just sql.
Posted: Sun May 15, 2005 9:19 pm
by anthony88guy
I would create unix timestamps. Much easier to work with, and use the date function to print them the way you'd like. Would make it easy to search also.
I would think you should be able to search. Only way to know is try it.
Posted: Sun May 15, 2005 9:35 pm
by bluesman333
That's normally how I do it, but in this situation, the data is being exported from another DB as 1/1/2005.
I wanted to toss around the idea of using the data in this format to avoid having to convert it.
Posted: Mon May 16, 2005 1:06 am
by anjanesh
Use MySQL
string functions ( LEFT, RIGHT, MID etc) and use them to extract the day, month and year and CONCAT them as any of the MySQL datatype and then do your adding / checking etc.
Note : Since your date is like 1/1/2005 and 01/01/2005 - the position of day and month will vary - use INSTR to figure out the positions of two slashes (/)