[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
[PHP] PHP MySQL DATE comparison
Hello, I'm pulling a date in the datetime format via a MySQL query. Using PHP I would like to check if that date is prior to today's date. Can someone please help with this quick question. Thanks, Ryan
[PHP] Re: PHP MySQL DATE comparison
Ryan Schefke [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
Hello,
I'm pulling a date in the datetime format via a MySQL query. Using PHP
I
would like to check if that date is prior to today's date. Can someone
please help with this quick question.
You could convert it to a timestamp via strtotime() and check it against
time():
$date = strtotime($dateFromDb);
if ($date time()) {
echo 'Date is in the past.';
}
Regards, Torsten Roehr
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] PHP MySQL DATE comparison
Hi All,
Answering my own question here, maybe this will help others out:
I found a nifty function below that will return seconds from Unix epoch to a
mysql value in the datetime format. I'll just compare this to the php
function time(). Let me know if anyone has a better solution.
?
# C2001 CodeHeadz.net
# Function is released under the GPL and is free as long as
# this header remains intact
#
function datetime_to_epoch($date) {
$break = explode( , $date);
$datebreak = explode(-, $break[0]);
$time = explode(:, $break[1]);
$epoch = date(U,
mktime($time[0],$time[1],$time[2],$datebreak[1],$datebreak[2],$datebreak[0])
);
$datetime = date(Y-m-d H:i:s,
mktime($time[0],$time[1],$time[2],$datebreak[1],$datebreak[2],$datebreak[0])
);
}
?
-Original Message-
From: Ryan Schefke [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 16, 2004 12:43 PM
To: Php-General-Help
Subject: [PHP] PHP MySQL DATE comparison
Hello,
I'm pulling a date in the datetime format via a MySQL query. Using PHP I
would like to check if that date is prior to today's date. Can someone
please help with this quick question.
Thanks,
Ryan
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] MySQL Date
Hi,
I have a date stored in a table in my MySQL Database using a DATE type for
the column.
How can i compare the date in the table to today
e.g.
$today = mysql_result($result, $i, Booking_Date);
if($today = *HELP*){
echo you are booked today;
}else{
echo you are free today;
}
thanks for your help
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] MySQL Date
Shaun,
It should be: date(Y-m-d). MySQL stores the dates as -mm-dd, and that function
returns the current date on the format you need.
Check the date() function on the php manual.
--
Un gran saludo/Big regards...
Arturo Barajas, IT/Systems PPG MX (SJDR)
(427) 271-9918, x448
-Original Message-
From: shaun [mailto:[EMAIL PROTECTED]
Sent: Miercoles, 19 de Marzo de 2003 07:19 a.m.
To: [EMAIL PROTECTED]
Subject: [PHP] MySQL Date
Hi,
I have a date stored in a table in my MySQL Database using a
DATE type for
the column.
How can i compare the date in the table to today
e.g.
$today = mysql_result($result, $i, Booking_Date);
if($today = *HELP*){
echo you are booked today;
}else{
echo you are free today;
}
thanks for your help
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] MySQL Date
I have a date stored in a table in my MySQL Database using a DATE type
for
the column.
How can i compare the date in the table to today
... WHERE your_date = CURDATE() ...
e.g.
$today = mysql_result($result, $i, Booking_Date);
if($today = *HELP*){
or...
if($today == date('Y-m-d'))
(notice the second equals sign!)
---John W. Holmes...
PHP Architect - A monthly magazine for PHP Professionals. Get your copy
today. http://www.phparch.com/
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Mysql Date problem...weird..
Hello,
Please read the below query and the information that I have mentioned. This
is really weird...
select date_format(date_add(arrivaldate1, INTERVAL nights1 DAY), '%Y-
%m-%d') as dept_date1 from mytable where ('dept_date1' BETWEEN
'2003-02-01' AND '2003-02-10')
When it goes to database the values will be substituted as follows for 1
row:
select date_format(date_add('2003-01-31', INTERVAL 5 DAY), '%Y-%m-%d')
as dept_date1 from mytable where ('dept_date1' BETWEEN '2003-02-01' AND
'2003-02-10')
which should return 1 row since, the query will return
dept_date1 = '2003-02-05' which falls between the dates '2003-02-01' AND
'2003-02-10'
but I am getting 0 result.
SO then I tried,
select date_format(date_add('2003-01-31', INTERVAL 5 DAY), '%Y-%m-%d')
as dept_date1 from mytable where ('dept_date1' = '2003-02-01')
IT WORKED!!!
then I tried,
select date_format(date_add('2003-01-31', INTERVAL 5 DAY), '%Y-%m-%d')
as dept_date1 from mytable where ('dept_date1' = '2003-02-10')
IT gave 0 result
then I tried,
select date_format(date_add('2003-01-31', INTERVAL 5 DAY), '%Y-%m-%d')
as dept_date1 from mytable where ('dept_date1' ='2003-02-01' AND
'dept_date1' = '2003-02-10')
IT gave 0 result
What could be the problem, could anybody help me please...
-Dhaval
_
STOP MORE SPAM with the new MSN 8 and get 2 months FREE*
http://join.msn.com/?page=features/junkmail
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Mysql Date got prob!
Hello,
As related to my earlier question
select date_format(date_add(arrivaldate1, INTERVAL nights1 DAY), '%Y-
%m-%d') as dept_date1 from mytable where ('dept_date1' BETWEEN
'2003-02-01' AND '2003-02-10')
The above query is valid but returns 0 because 'dept_date1' is treated as a
string. I want dept_date1 to be treated as date so that it can be compared.
I hope it is possible...
Thank you!
-Dhaval
_
Add photos to your messages with MSN 8. Get 2 months FREE*.
http://join.msn.com/?page=features/featuredemail
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Mysql date format
I'm using php with MySql. I'm migrating an php-oracle application to php-mysql. I need to know if exists any function in mysql similar to the to_char or to_date functions of oracle; or format function of access?. These functions make a date format or numeric format. ex: to_char(sysdate,'dd/mm/ hh:mi:ss') return a string with the actual datetime in the specified format. Thanks, RB _ Únete al mayor servicio mundial de correo electrónico: http://www.hotmail.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Mysql date format
I'm using php with MySql. I'm migrating an php-oracle application to php-mysql. I need to know if exists any function in mysql similar to the to_char or to_date functions of oracle; or format function of access?. These functions make a date format or numeric format. ex: to_char(sysdate,'dd/mm/ hh:mi:ss') return a string with the actual datetime in the specified format. MySQL has the DATE_FORMAT() function to do this. http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html #Date_and_time_functions ---John Holmes... -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] PHP/MySQL -- Date/Time Confusion
Which method is the best way to store date/time in MySQL and then which PHP command is the best to use to convert that date to something useful? I'm having a difficult time figuring out how to reconcile the date in MySQL so it works with PHP's various date commands, like getdate(). Any suggestions? I'll mostly need to compared dates/times of different records and then extract parts of the date for displaying on the screen. Thanks! Monty -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP/MySQL -- Date/Time Confusion
You've got all kinds of options. I suggest you do any date calculations in the database and use DATE_FORMAT to format any dates pulled out. It's exactly like the date() function in PHP. MySQL has some great date and time functions so you can select out exactly what you want. You can use the UNIXTIME() (I think that's the function) function, too, to convert the MySQL timestamp to a unix timestamp. It's all in the MySQL manual, chapter 6, Date and Time Functions. ---John Holmes... - Original Message - From: Monty [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, July 12, 2002 5:06 PM Subject: [PHP] PHP/MySQL -- Date/Time Confusion Which method is the best way to store date/time in MySQL and then which PHP command is the best to use to convert that date to something useful? I'm having a difficult time figuring out how to reconcile the date in MySQL so it works with PHP's various date commands, like getdate(). Any suggestions? I'll mostly need to compared dates/times of different records and then extract parts of the date for displaying on the screen. Thanks! Monty -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] PHP/MySQL -- Date/Time Confusion
Which method is the best way to store date/time in MySQL and then which PHP command is the best to use to convert that date to something useful? Get comfortable with MySQL's date functionality and you'll find that you don't often need to do the date comparisons and manipulation in php at all. MySQL lets you store date/time stuff as date, time or timestamp formats (off-hand, haven't referred to the manual here) that will store the data for you. You can manipulate this date information in your sql query using things like DATE_FORMAT() and the like. Check the MySQL manual for all of those (chapter 6 or 7 I think it is). If you're mostly doing data extraction based on date criteria then you'll find it works best as sql queries basically. CYA, Dave -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP/MySQL -- Date/Time Confusion
The easiest and my preferred way is to use strtotim method. This method accepts MySQL format 2002-12-23 and converts it into a UNIX time stamp. After that you can use the date function to format time as you wish! Monty [EMAIL PROTECTED] wrote: Which method is the best way to store date/time in MySQL and then which PHP command is the best to use to convert that date to something useful? I'm having a difficult time figuring out how to reconcile the date in MySQL so it works with PHP's various date commands, like getdate(). Any suggestions? I'll mostly need to compared dates/times of different records and then extract parts of the date for displaying on the screen. Thanks! Monty -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php - Do You Yahoo!? New! SBC Yahoo! Dial - 1st Month Free unlimited access
[PHP] MySQL date and NULL problems
Hi again,
A few days ago, I sent in an inquiry (see below) about how to make the date
field in mysql read NULL when the corresponding form field was empty. I
tried the suggestions I received and the following if statements all seem to
still result in a default -00-00 instead of NULL.
/ this enters NULL if event_date is empty
if ($event_date == ) $event_date = '\0';
if ($event_date == ) $event_date = \0;
if ($event_date == ) $event_date = \0;
if ($event_date == ) $event_date = NULL;
if ($event_date == ) $event_date = '\0';
// here's the insert statement
mysql_db_query($db, insert into $table (id,
approved, author, title,
featured, keywords, description, content, type, category, datetime, country,
region, event_date) values(null, '$approved', $author, '$title',
date_add('$datetime', interval $featured day), '$keywords', '$description',
'$content', '$type', '$category', '$datetime', '$country', '$region',
'$event_date'));
I'd really appreciate your suggestions and help. I can work around this
result, but it's really bugging me now why I can't get the word 'NULL' in
the date field when the corresponding form field is empty.
thank you, Shawna
--
From: [EMAIL PROTECTED]
Sent: Tuesday, December 4, 2001 5:31 PM
To: [EMAIL PROTECTED]
Subject: [PHP] Simple Question: PHP, MySQL, HTML Form and NULL
I have added a new column in an existing MYSQL table called event_date
-
type: DATE, Null - yes ; default - NULL.
This database field will be filled if and when the field in the HTML form
is
complete. When I added the new field to MySQL, it automatically assigned
NULL to all the existing records - which is what I wanted it to do.
I tested the HTML form, and when someone enters a date in the HTML form,
the
date appears correctly in the table field. Perfect.
Now my question: When the HTML form date field is left blank and the form
is submitted, instead of putting NULL in the MySQL event_date field, I
found: -00-00. Is this because the form is submitting an to the
database field?
Is there some kind of if/then statement I should use so that when the
field
is empty, NULL will be entered into that table field?
Thank you, Shawna
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] mySQL date converting
Hi, You could write a PHP script to get all the current values (I presume that you've put varchar as datatype), parse them with string functions and convert it in the way you'd like them to be. All this is not so difficult to make. I prefer to store my date/times in the UNIX timestamp form. I use an int(10) field in mySQL database (int(10) means an integer with 10 digits, this way I can store more than 331 years!). This is easy to sort and to calculate with too. In your development you can use this timestamp and format it in any way you want it using the date() function. You'll find all references in the php.net manual. Greetz, Bjorn Van Simaeys www.bvsenterprises.com --- Christopher CM Allen [EMAIL PROTECTED] wrote: Greetings: I have a field entry in a MYQSL table that stores the date as: August 02, 2001 :( Is there anyway to convert this to 08-02-2001 outside of the table via php? Better yet is there ayway I can go back into my table and change these dates into a better format for computation? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
