[PHP] mysql date question
I have a field in mysql as shown by describe contract; | length_start | date| YES | | NULL || Which stores it in the mysql format of -MM-DD. However, I need the output of my select statement to show it in MM-DD- format. I can select it to see the date in the field: select length_start from contract where user_id = 1; +--+ | length_start | +--+ | 2006-01-12 | +--+ 1 row in set (0.00 sec) so then I do my date_format() select statement, but it returns a NULL value. Why? select date_format('contract.length_start', '%m-%d-%Y') as length_start from contract where user_id = 1; +--+ | length_start | +--+ | NULL | +--+ 1 row in set, 1 warning (0.00 sec) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql date question
Adam Williams wrote: select date_format('contract.length_start', '%m-%d-%Y') as length_start from contract where user_id = 1; This has nothing to do with PHP, but the first parameter to date_format should not be in quotes. -Stut -- http://stut.net/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] mysql date question
no need for quotes select date_format(contract.length_start, '%m-%d-%Y') as length_start from contract where user_id = 1; bastien Date: Thu, 3 Jan 2008 08:30:55 -0600 From: [EMAIL PROTECTED] To: php-general@lists.php.net Subject: [PHP] mysql date question I have a field in mysql as shown by describe contract; | length_start | date | YES | | NULL | | Which stores it in the mysql format of -MM-DD. However, I need the output of my select statement to show it in MM-DD- format. I can select it to see the date in the field: select length_start from contract where user_id = 1; +--+ | length_start | +--+ | 2006-01-12 | +--+ 1 row in set (0.00 sec) so then I do my date_format() select statement, but it returns a NULL value. Why? select date_format('contract.length_start', '%m-%d-%Y') as length_start from contract where user_id = 1; +--+ | length_start | +--+ | NULL | +--+ 1 row in set, 1 warning (0.00 sec) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php _ Discover new ways to stay in touch with Windows Live! Visit the City @ Live today! http://getyourliveid.ca/?icid=LIVEIDENCA006
RE: [PHP] mysql date question
[snip] I have a field in mysql as shown by describe contract; | length_start | date| YES | | NULL || Which stores it in the mysql format of -MM-DD. However, I need the output of my select statement to show it in MM-DD- format. I can select it to see the date in the field: select length_start from contract where user_id = 1; +--+ | length_start | +--+ | 2006-01-12 | +--+ 1 row in set (0.00 sec) so then I do my date_format() select statement, but it returns a NULL value. Why? select date_format('contract.length_start', '%m-%d-%Y') as length_start from contract where user_id = 1; +--+ | length_start | +--+ | NULL | +--+ 1 row in set, 1 warning (0.00 sec) [/snip] Actually this is more a question for the MySQL list. Start first by taking the ticks or quotes off of the column; select date_format(contract.length_start, '%m-%d-%Y') as length_start from contract where user_id = 1; You are essentially trying to turn that text string into a date, it will not work. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql date question
Uhm, a shot in the dark - try: select date_format(contract.length_start, '%m-%d-%Y') as length_start HTH, cheers! Silvio Adam Williams wrote: I have a field in mysql as shown by describe contract; | length_start | date| YES | | NULL || Which stores it in the mysql format of -MM-DD. However, I need the output of my select statement to show it in MM-DD- format. I can select it to see the date in the field: select length_start from contract where user_id = 1; +--+ | length_start | +--+ | 2006-01-12 | +--+ 1 row in set (0.00 sec) so then I do my date_format() select statement, but it returns a NULL value. Why? select date_format('contract.length_start', '%m-%d-%Y') as length_start from contract where user_id = 1; +--+ | length_start | +--+ | NULL | +--+ 1 row in set, 1 warning (0.00 sec) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql date question
On Thu, January 3, 2008 8:30 am, Adam Williams wrote: I have a field in mysql as shown by describe contract; | length_start | date| YES | | NULL || Which stores it in the mysql format of -MM-DD. However, I need the output of my select statement to show it in MM-DD- format. I can select it to see the date in the field: select length_start from contract where user_id = 1; +--+ | length_start | +--+ | 2006-01-12 | +--+ 1 row in set (0.00 sec) so then I do my date_format() select statement, but it returns a NULL value. Why? select date_format('contract.length_start', '%m-%d-%Y') as length_start from contract where user_id = 1; +--+ | length_start | +--+ | NULL | +--+ 1 row in set, 1 warning (0.00 sec) There is not PHP in this question. But to save you subscribing/posting/unsubcribing to the MySQL list: You put apostrophes on 'contract.length_start' which makes it a literal DATE. MySQL silently ignores such a stupid-looking date, and makes it NULL. Take away the apostrophes on the FIELD NAME and all will be good. -- Some people have a gift link here. Know what I want? I want you to buy a CD from some indie artist. http://cdbaby.com/from/lynch Yeah, I get a buck. So? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Confused overSimple PHP mySQL date question
Hi, I have checked the recent list archives and looked up various PHP functions. I know what I want should be simple but, apparently not simple enought for me. I have a mysql database that has a date field and a time field. I want users to be able to enter a date and a time in text boxes on an html form and have them end up in the database. I am having no trouble connecting to the database and running queries against the database but I cannot get the dates and times into the database. Of course I also want to search for the database for an entered date on another form. I am not having trouble with SQL statements or mysql_connect() or anything like that just taking a date as a string from a text box and getting into a DATE field in a mysql database. What obvuios thing did I miss? Note I am not using the system date or time stamp, these are entered dates. Regards, Scott -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Confused overSimple PHP mySQL date question
[snip] DATE field in a mysql database. What obvuios thing did I miss? Note I am not using the system date or time stamp, these are entered dates. [/snip] You're using a DATE field in the MySQL database. MySQL requires an ISO formatted date unless you manipulate it, such as 2004-09-14 http://dev.mysql.com/doc/mysql/en/Date_and_time_types.html -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Confused overSimple PHP mySQL date question
From: Scott Miller [EMAIL PROTECTED] I have a mysql database that has a date field and a time field. I want users to be able to enter a date and a time in text boxes on an html form and have them end up in the database. Code examples would be good here. Either way, the format for a DATE field is MMDD or '-MM-DD' and the format for a TIME field is HHMMSS or 'HH:MM:SS'. Make sure what you're trying to stick in the database is in that format. If you want to accept another format in your text fields, then you'll need to use date(), strtotime(), mktime(), explode(), etc to format it this way. You really just need one DATETIME or TIMESTAMP field, though... there's reason to keep these values in two separate fields if they are related to the same event. It'll make searching down the road easier. SELECT * FROM events WHERE datetimecolumn BETWEEN NOW() AND INTERVAL + 30 DAY; SELECT * FROM events WHERE datetimecolumn BETWEEN 20040901 AND 20040930; etc... ---John Holmes... -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php