Query based on date 1

[novice2004]

I am trying to add date data to mySQL.
Upon adding info into database User selects start and end day when he wants his article to be visible.

I have to 2 questions:
1) What format should I save my date info in mySQL?
Is mm/dd/YYYY '10/31/2004' a good solution?

Users can do search and display data from certain date to certain date.

How do pros go about it? date >'10/31/2004' and date < '11/31/2004' ?



Any advice would be appreciated.

Thank you.

 

[ericbrunson]

Save it as a "date" type, don't save it as a string.

 

[esdee]

i personally use timestamp column type

------------------------
dev at viewmoresoft.com

http://www.viewmoresoft.com

tools for your database

 

[ericbrunson]

Personal preference.

 

[novice2004]

Thank you but nobody really answered my question.
I use "date" type but how do I display all articles that
are eligible.

I have 2 fields("date" type):
start day end day


Lets say today is '10/31/2004'
when I do SELECT query
article with start day '10/20/2004'
and end day '11/2/2004'gets displayed
but
article with start day '11/1/2004'
and end day '11/3/2004'do not get displayed.

How do I go about SELECT query?

 

[swampBoogie]

Use yyyy-mm-dd format.

date >'2004-10-31' and date < '2004-11-31'

 

[r937]

where current_date between startday and endday

rudy

http://r937.com/

SQL Consulting

 

[ericbrunson]

I'm sorry, I thought by pointing out the existence of a field type "date" you might actually open a manual and read something about it. It's all in there.

 

[novice2004]

Thank you everybody.
Everybody is pointing me to the same direction.
How about solution like that:
First second of the new day there is a script automatically running and setting the field to 1 if the now date is between date fields start and end
and to 0 if not.
Then Wouldn't search be faster?(I have more constraints than just date)
Wouldn't search be faster?
Is anybody using this solution?

 

[novice2004]

I mean field visible = 1 or 0.
Than SELECT * WHERE VISIBLE = 1.

 

[r937]

never mind the flag

just search on the date fields

make sure they're indexed

rudy

http://r937.com/

SQL Consulting

 

[novice2004]

Thank you.