PHP & MYSQL DATE Function?

BuLLDoG · 11-02-2009, 08:43 AM

Hi,

I am trying to insert a date into a field in the format of YYYY-MM-DD. I have tried several ways, but it isn't inserting into the database correctly.

My SQL statement I am using is:

PHP Code:

  $sql="INSERT INTO $tbl_name(itpname, detail, author, externalurl, datetime)VALUES('$itpname', '$detail', '$author', '$externalurl', curdate())"; 

????: NamePros.com http://www.namepros.com/programming/620615-php-and-mysql-date-function.html

but when I am viewing it in phpmyadmin I see the result:

02/11/09 1

My field has been set to type VARCHAR.

Anyone know how to do it?

demaxxus · 11-02-2009, 08:56 AM

If you're going to store your dates in the format YYYY-MM-DD you should probably use the date type instead of varchar. You'll find that you'll run into all sorts of problems using varchar if you're trying to query on those dates.

Also, depending on what type of data you're storing in this table, you may also want to think about using the datetime type, which will save the full date and time in the format YYYY-MM-DD HH:MM:SS.

BuLLDoG · 11-02-2009, 09:11 AM

If I set the field to 'date' type, how should my sql statement look?

---------- Post added at 05:11 PM ---------- Previous post was at 05:05 PM ----------

Because, I have just changed field type to 'datetime'

And used the following code:

PHP Code:

  $datestamp=date("y-m-d h:i:s"); //create date time

????: NamePros.com http://www.namepros.com/showthread.php?t=620615
 
 
$sql="INSERT INTO $tbl_name(itpname, detail, author, externalurl, datetime)VALUES('$itpname', '$detail', '$author', '$externalurl', $datestamp)";

and the result I see in phpmyadmin is now: 2002-11-09 11:08:36

I want it to be displayed as YYYY-MM-DD HH:MM:SS
but as you can see its put it in the wrong order?

demaxxus · 11-02-2009, 09:11 AM

Originally Posted by BuLLDoG

If I set the field to 'date' type, how should my sql statement look?

curdate() already returns the date in the YYYY-MM-DD format, so your current query should work as is.

BuLLDoG · 11-02-2009, 09:15 AM

Even with curdate() its putting it as 2002-11-09 :S

demaxxus · 11-02-2009, 09:21 AM

For your question about datetime, try using the following format for your php date function - "Y-m-d H:i:s" (capital Y and H).

In other words, use this:

Code:

$datestamp=date("Y-m-d H:i:s");

$sql="INSERT INTO $tbl_name(itpname, detail, author, externalurl, datetime)VALUES('$itpname', '$detail', '$author', '$externalurl', '$datestamp')";

BuLLDoG · 11-02-2009, 09:21 AM

Same problem is happening...

demaxxus · 11-02-2009, 09:29 AM

Now that you're using the php date function and then inserting the variable into the db, instead of running curtime, you'll have to put ' ' around the $datestamp variable when inserting.

BuLLDoG · 11-02-2009, 09:37 AM

Yup, it has one but still giving the same problem? quite strange I didn't think dates would be this problematic

demaxxus · 11-02-2009, 09:45 AM

That's really bizarre, it should be working. Try narrowing it down first...

Replace:

Code:

$datestamp=date("Y-m-d H:i:s");

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

Code:

$datestamp=date("Y-m-d H:i:s"); 
echo $datestamp; 
exit;

When you load that page in your browser is it displaying the date and time correctly?

BuLLDoG · 11-02-2009, 09:59 AM

it works whilst I echo it out, just when it gets added to the database I hit the problem..

demaxxus · 11-02-2009, 10:02 AM

Try the same with the insert statement.

Code:

$sql="INSERT INTO $tbl_name(itpname, detail, author, externalurl, datetime) VALUES ('$itpname', '$detail', '$author', '$externalurl', '$datestamp')";
echo $sql;
exit;

If that's displaying properly, and the table field is set to use the datetime type, I'm not really sure what to tell you, the code you have should be working.

BuLLDoG · 11-02-2009, 10:11 AM

Ah school boy error, it is working now, the type was date instead of datetime my bad. Thanks ever so much for your help demaxxus

sdsinc · 11-02-2009, 10:48 AM

Instead of formatting the date in PHP you can also do it with mySQL using the DATE_FORMAT function.

nasaboy007 · 11-02-2009, 05:14 PM

i believe the mysql NOW() function also does the same thing to get datetime.

i personally always just keep it in terms of epoch time and then use php functions like date and mktime to go back and forth between epoch and readable time. it's MUCH easier to do computations with epoch time, so if you'll be doing things like time between datetimes etc, i'd suggest looking into it.

11-02-2009, 08:43 AM	THREAD STARTER #1 (permalink)
BuLLDoG NamePros Regular Join Date: Aug 2003 Location: England Posts: 934	PHP & MYSQL DATE Function? Hi, I am trying to insert a date into a field in the format of YYYY-MM-DD. I have tried several ways, but it isn't inserting into the database correctly. My SQL statement I am using is: PHP Code: `$sql="INSERT INTO $tbl_name(itpname, detail, author, externalurl, datetime)VALUES('$itpname', '$detail', '$author', '$externalurl', curdate())"; ????: NamePros.com http://www.namepros.com/programming/620615-php-and-mysql-date-function.html` but when I am viewing it in phpmyadmin I see the result: 02/11/09 1 My field has been set to type VARCHAR. Anyone know how to do it? __________________ Free iPhone \| Free Facebook Likes

11-02-2009, 09:11 AM	THREAD STARTER #3 (permalink)
BuLLDoG NamePros Regular Join Date: Aug 2003 Location: England Posts: 934	If I set the field to 'date' type, how should my sql statement look? ---------- Post added at 05:11 PM ---------- Previous post was at 05:05 PM ---------- Because, I have just changed field type to 'datetime' And used the following code: PHP Code: `$datestamp=date("y-m-d h:i:s"); //create date time ????: NamePros.com http://www.namepros.com/showthread.php?t=620615 $sql="INSERT INTO $tbl_name(itpname, detail, author, externalurl, datetime)VALUES('$itpname', '$detail', '$author', '$externalurl', $datestamp)";` and the result I see in phpmyadmin is now: 2002-11-09 11:08:36 I want it to be displayed as YYYY-MM-DD HH:MM:SS but as you can see its put it in the wrong order? __________________ Free iPhone \| Free Facebook Likes

11-02-2009, 09:15 AM	THREAD STARTER #5 (permalink)
BuLLDoG NamePros Regular Join Date: Aug 2003 Location: England Posts: 934	Even with curdate() its putting it as 2002-11-09 :S __________________ Free iPhone \| Free Facebook Likes

11-02-2009, 09:21 AM	#6 (permalink)
demaxxus NamePros Regular Join Date: Jul 2007 Posts: 323	For your question about datetime, try using the following format for your php date function - "Y-m-d H:i:s" (capital Y and H). In other words, use this: Code: $datestamp=date("Y-m-d H:i:s"); $sql="INSERT INTO $tbl_name(itpname, detail, author, externalurl, datetime)VALUES('$itpname', '$detail', '$author', '$externalurl', '$datestamp')"; Last edited by demaxxus; 11-02-2009 at 09:30 AM.

11-02-2009, 09:21 AM	THREAD STARTER #7 (permalink)
BuLLDoG NamePros Regular Join Date: Aug 2003 Location: England Posts: 934	Same problem is happening... __________________ Free iPhone \| Free Facebook Likes

11-02-2009, 08:56 AM	#2 (permalink)
demaxxus NamePros Regular Join Date: Jul 2007 Posts: 323	If you're going to store your dates in the format YYYY-MM-DD you should probably use the date type instead of varchar. You'll find that you'll run into all sorts of problems using varchar if you're trying to query on those dates. Also, depending on what type of data you're storing in this table, you may also want to think about using the datetime type, which will save the full date and time in the format YYYY-MM-DD HH:MM:SS.

11-02-2009, 09:29 AM	#8 (permalink)
demaxxus NamePros Regular Join Date: Jul 2007 Posts: 323	Now that you're using the php date function and then inserting the variable into the db, instead of running curtime, you'll have to put ' ' around the $datestamp variable when inserting.

11-02-2009, 09:37 AM	THREAD STARTER #9 (permalink)
BuLLDoG NamePros Regular Join Date: Aug 2003 Location: England Posts: 934	Yup, it has one but still giving the same problem? quite strange I didn't think dates would be this problematic __________________ Free iPhone \| Free Facebook Likes

11-02-2009, 09:45 AM	#10 (permalink)
demaxxus NamePros Regular Join Date: Jul 2007 Posts: 323	That's really bizarre, it should be working. Try narrowing it down first... Replace: Code: $datestamp=date("Y-m-d H:i:s"); With: ????: NamePros.com http://www.namepros.com/showthread.php?t=620615 Code: $datestamp=date("Y-m-d H:i:s"); echo $datestamp; exit; When you load that page in your browser is it displaying the date and time correctly?

11-02-2009, 09:59 AM	THREAD STARTER #11 (permalink)
BuLLDoG NamePros Regular Join Date: Aug 2003 Location: England Posts: 934	it works whilst I echo it out, just when it gets added to the database I hit the problem.. __________________ Free iPhone \| Free Facebook Likes

11-02-2009, 10:02 AM	#12 (permalink)
demaxxus NamePros Regular Join Date: Jul 2007 Posts: 323	Try the same with the insert statement. Code: $sql="INSERT INTO $tbl_name(itpname, detail, author, externalurl, datetime) VALUES ('$itpname', '$detail', '$author', '$externalurl', '$datestamp')"; echo $sql; exit; If that's displaying properly, and the table field is set to use the datetime type, I'm not really sure what to tell you, the code you have should be working.

11-02-2009, 10:11 AM	THREAD STARTER #13 (permalink)
BuLLDoG NamePros Regular Join Date: Aug 2003 Location: England Posts: 934	Ah school boy error, it is working now, the type was date instead of datetime my bad. Thanks ever so much for your help demaxxus __________________ Free iPhone \| Free Facebook Likes

11-02-2009, 10:48 AM	#14 (permalink)
sdsinc Domains my Dominion Join Date: Aug 2005 Location: Web 1.0 Posts: 9,963	Instead of formatting the date in PHP you can also do it with mySQL using the DATE_FORMAT function. __________________ NameNewsletter.com - free lists of available domain names ZoneFiles.net (beta) - ccTLD and gTLD droplists

11-02-2009, 05:14 PM	#15 (permalink)
nasaboy007 Senior Member Join Date: Jul 2005 Location: NJ Posts: 1,219	i believe the mysql NOW() function also does the same thing to get datetime. i personally always just keep it in terms of epoch time and then use php functions like date and mktime to go back and forth between epoch and readable time. it's MUCH easier to do computations with epoch time, so if you'll be doing things like time between datetimes etc, i'd suggest looking into it. __________________ Hacksar.com - Your source for random computer tips and tricks! MySiteMemberships.com - Keep track of your site registration information! Like my post? Rep is appreciated!

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