Date Query Problem

shahid_146 · 10-24-2005, 05:56 AM

I have got date stored in MySQL in this format "Sat, 22 Oct 2005 - 19:30:00" and I want to write a query to select ALL records with DATE coming AFTER the CURRENT DATE or TODAY. In other words I want to select all future dates only. Can anyone help ?

http://www.binarytrends.com

sdsinc · 10-24-2005, 05:10 PM

Like this ?

PHP Code:

  select * from your_table  where date_field > CURDATE()

InvertedVertex · 10-24-2005, 07:29 PM

Would this work?

Code:

SELECT * from your_table WHERE date_field > date('D, j M Y - h:i:s')

The date is formatted the way you have it.

shahid_146 · 10-24-2005, 11:27 PM

thanks but i found that i was storing the date in a varchar field and mysql won't do a date calculation on a varchar in an english formated date. So I have to handle it with code in php.

sdsinc · 10-25-2005, 07:23 AM

Shahid,
May I ask why you don't store the date field as datetime ?
You can use the my_sql date_format function to format the display of the date in your queries then.

shahid_146 · 10-26-2005, 12:04 AM

I made a mistake long ago by making this field a varchar and storing the date and time in a english style date and now I got in trouble. But I have finally got round it

Thanks everyone. I finally got round it by using this :

DATEDIFF(str_to_date(right(myDateField, 22),'%d %b %Y'),CURDATE())>0

So if anyone ever makes a mistakes by making a date field VARCHAR, this is the solution to compare it with current date

Thanks

http://www.binarytrends.com

sdsinc · 10-26-2005, 03:09 AM

Fine. If I were you I would run a query to convert the values to a more convenient (date) format

10-24-2005, 05:56 AM	#1 (permalink)
shahid_146 NamePros Member Join Date: Sep 2005 Posts: 30 57.00 NP$ (Donate)	Date Query Problem I have got date stored in MySQL in this format "Sat, 22 Oct 2005 - 19:30:00" and I want to write a query to select ALL records with DATE coming AFTER the CURRENT DATE or TODAY. In other words I want to select all future dates only. Can anyone help ? http://www.binarytrends.com

10-24-2005, 05:10 PM	#2 (permalink)
sdsinc Domains my Dominion Join Date: Aug 2005 Location: Web 1.0 Posts: 6,284 1,095.94 NP$ (Donate)	Like this ? PHP Code: `select * from your_table where date_field > CURDATE()` __________________ Buy now - MassDeveloper.com $500

10-24-2005, 07:29 PM	#3 (permalink)
InvertedVertex NamePros Member Join Date: May 2005 Location: USA Posts: 91 858.40 NP$ (Donate)	Would this work? Code: SELECT * from your_table WHERE date_field > date('D, j M Y - h:i:s') The date is formatted the way you have it. __________________ Girl Arcades.com - Girl Themed Topsite JOIN NOW TalkChevelles.com for sale. FlameGames.com - Online Arcade

10-24-2005, 11:27 PM	#4 (permalink)
shahid_146 NamePros Member Join Date: Sep 2005 Posts: 30 57.00 NP$ (Donate)	date query thanks but i found that i was storing the date in a varchar field and mysql won't do a date calculation on a varchar in an english formated date. So I have to handle it with code in php.

10-25-2005, 07:23 AM	#5 (permalink)
sdsinc Domains my Dominion Join Date: Aug 2005 Location: Web 1.0 Posts: 6,284 1,095.94 NP$ (Donate)	Shahid, May I ask why you don't store the date field as datetime ? You can use the my_sql date_format function to format the display of the date in your queries then. __________________ Buy now - MassDeveloper.com $500

10-26-2005, 12:04 AM	#6 (permalink)
shahid_146 NamePros Member Join Date: Sep 2005 Posts: 30 57.00 NP$ (Donate)	I made a mistake long ago by making this field a varchar and storing the date and time in a english style date and now I got in trouble. But I have finally got round it Thanks everyone. I finally got round it by using this : DATEDIFF(str_to_date(right(myDateField, 22),'%d %b %Y'),CURDATE())>0 So if anyone ever makes a mistakes by making a date field VARCHAR, this is the solution to compare it with current date Thanks http://www.binarytrends.com

10-26-2005, 03:09 AM	#7 (permalink)
sdsinc Domains my Dominion Join Date: Aug 2005 Location: Web 1.0 Posts: 6,284 1,095.94 NP$ (Donate)	Fine. If I were you I would run a query to convert the values to a more convenient (date) format __________________ Buy now - MassDeveloper.com $500

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
soooo many yahoo's	redhippo	Domain Name Discussion	5	07-12-2005 11:26 PM
ReleaseDates.com	jumpbug	Domain Appraisals	11	06-30-2005 09:37 AM