Date Query Problem

shahid_146 · 10-24-2005, 06: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, 06:10 PM

Like this ?

PHP Code:

  select * from your_table  where date_field > CURDATE() 

????: NamePros.com http://www.namepros.com/programming/134491-date-query-problem.html

InvertedVertex · 10-24-2005, 08: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-25-2005, 12:27 AM

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, 08: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, 01: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

????: NamePros.com http://www.namepros.com/showthread.php?t=134491

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, 04: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, 06:56 AM	THREAD STARTER #1 (permalink)
shahid_146 NamePros Member Join Date: Sep 2005 Posts: 30	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, 06:10 PM	#2 (permalink)
sdsinc Domains my Dominion Join Date: Aug 2005 Location: Web 1.0 Posts: 9,552	Like this ? PHP Code: `select * from your_table where date_field > CURDATE() ????: NamePros.com http://www.namepros.com/programming/134491-date-query-problem.html` __________________ NameNewsletter.com - free lists of available domain names ZoneFiles.net (beta) - ccTLD and gTLD droplists

10-24-2005, 08:29 PM	#3 (permalink)
InvertedVertex NamePros Member Join Date: May 2005 Location: USA Posts: 93	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-25-2005, 12:27 AM	THREAD STARTER #4 (permalink)
shahid_146 NamePros Member Join Date: Sep 2005 Posts: 30	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, 08:23 AM	#5 (permalink)
sdsinc Domains my Dominion Join Date: Aug 2005 Location: Web 1.0 Posts: 9,552	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. __________________ NameNewsletter.com - free lists of available domain names ZoneFiles.net (beta) - ccTLD and gTLD droplists

10-26-2005, 01:04 AM	THREAD STARTER #6 (permalink)
shahid_146 NamePros Member Join Date: Sep 2005 Posts: 30	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 ????: NamePros.com http://www.namepros.com/showthread.php?t=134491 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, 04:09 AM	#7 (permalink)
sdsinc Domains my Dominion Join Date: Aug 2005 Location: Web 1.0 Posts: 9,552	Fine. If I were you I would run a query to convert the values to a more convenient (date) format __________________ NameNewsletter.com - free lists of available domain names ZoneFiles.net (beta) - ccTLD and gTLD droplists

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-13-2005 12:26 AM
ReleaseDates.com	jumpbug	Domain Appraisals	11	06-30-2005 10:37 AM
ReleaseDates.com	jumpbug	Domains For Sale - Make Offer	0	06-29-2005 09:03 PM

Date Query Problem

date query