Unix timestamp, formatted date and MySQL 
Author Message
 Unix timestamp, formatted date and MySQL

Hey, I have my MySQL database with a field called "validTo" which is of
fieldtype datetime.
Im displaying dates in this format: "28/11/2002 14:45:51"

Im using the function dateToUnix() to make this date format into a
timestamp. Then i reformat with "date(Y-m-d H:i:s)" to make it a format
understandable for MySQL.

When I try to update MySQL with the format created ("2002-11-28 14:45:51") I
get this msg: You have an error in your SQL syntax near '14:45:51,
priority='3' WHERE newsId=3' at line 1

I cannot find why this is illegal syntax, would anyone pls enlighten me?
I have another similar field where I only do a date(Y-m-d H:i:s) and it
works fine... also a datetime type column

These are the var values:
validstamp= 1038491151
validto= 28/11/2002 14:45:51
datestamp= 2002-11-07 14:53 // = time()
fdate= 2002-11-28 14:45:51

Here is the code, stripped down to a minimum for you, let me know if you
want the full version; http://www.*-*-*.com/

thanks in advance, Chris



Mon, 25 Apr 2005 22:03:06 GMT  
 Unix timestamp, formatted date and MySQL
The easiest thing to do is make the PHP script echo the MySQL query instead
of actually executing it. I'm led to believe that the dates are not the
problem, or in any case, not the only problem.

Bob


Quote:
> Hey, I have my MySQL database with a field called "validTo" which is of
> fieldtype datetime.
> Im displaying dates in this format: "28/11/2002 14:45:51"

> Im using the function dateToUnix() to make this date format into a
> timestamp. Then i reformat with "date(Y-m-d H:i:s)" to make it a format
> understandable for MySQL.

> When I try to update MySQL with the format created ("2002-11-28 14:45:51")
I
> get this msg: You have an error in your SQL syntax near '14:45:51,
> priority='3' WHERE newsId=3' at line 1

> I cannot find why this is illegal syntax, would anyone pls enlighten me?
> I have another similar field where I only do a date(Y-m-d H:i:s) and it
> works fine... also a datetime type column

> These are the var values:
> validstamp= 1038491151
> validto= 28/11/2002 14:45:51
> datestamp= 2002-11-07 14:53 // = time()
> fdate= 2002-11-28 14:45:51

> Here is the code, stripped down to a minimum for you, let me know if you
> want the full version; http://www.hybeko.no/help.phps

> thanks in advance, Chris



Tue, 26 Apr 2005 03:14:20 GMT  
 Unix timestamp, formatted date and MySQL

Quote:

> When I try to update MySQL with the format created ("2002-11-28 14:45:51")
> I get this msg: You have an error in your SQL syntax near '14:45:51,
> priority='3' WHERE newsId=3' at line 1

> I cannot find why this is illegal syntax, would anyone pls enlighten me?

OK, here's the line of code that seems to stop the show:

$sql = "UPDATE news SET datestamp='$datestamp', validTo=$fdate,
        priority='$priority' WHERE newsId=$newsId";

Notice that all variables but $fdate are enclosed in single quotes.
Will it help if you enclose $fdate in single quotes as well?  Like
this:

$sql = "UPDATE news SET datestamp='$datestamp', validTo='$fdate',
        priority='$priority' WHERE newsId=$newsId";

Cheers,
NC



Tue, 26 Apr 2005 06:06:11 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. How to display date from unix format to dd/mm/yy format

2. Changing date format when doing MySQL query?

3. Formatting dates from MySQL

4. mysql and DATE format

5. DATE and TimeStamp

6. TimeStamp class>>date:time:

7. TimeStamp, Date and Time Thoughts...

8. TimeStamp, Date and Time Thoughts...

9. Invalid dates - 215 using Btrieve/Clipper DATE format

10. How to add date/timestamp in TPS records.

11. date() use with array of unix dates

12. Timestamp vs Date

 

 
Powered by phpBB® Forum Software