searching by date

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
bluesman333
Forum Commoner
Posts: 52
Joined: Wed Dec 31, 2003 9:47 am

searching by date

Post 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?
SBro
Forum Commoner
Posts: 98
Joined: Tue Sep 30, 2003 10:06 pm

Post 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.
anthony88guy
Forum Contributor
Posts: 246
Joined: Thu Jan 20, 2005 8:22 pm

Post 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.
User avatar
bluesman333
Forum Commoner
Posts: 52
Joined: Wed Dec 31, 2003 9:47 am

Post 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.
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post 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 (/)
Post Reply