[Resolved] date/time problem

Velausanakha

$SQL_Exec_String = "Insert Into Person ( PersonID, Title, FamilyName, GivenName, PreferredName, Password, DateOfBirth, Mobile, DateExpired, FirstJoined) Values ( $PersonID, '$_POST[Title]', '$_POST[Surname]', '$_POST[FirstName]', '$_POST[Preferred]', '$_POST[Password]','$_POST[BirthDate]', '$_POST[Mobile]', date()+(365*5) , date())";

works perfectly in another file

$SQL_Exec_String = "Insert Into BookSpec ( PostedBy, BookSpecID, CourseCode, Author, Title, Price, Edition, DateListed, Hardcover, Bought) Values (".$_SESSION['ID'].", $BookSpecID, '$_POST[CourseCode]', '$_POST[Author]', '$_POST[Title]', '$_POST[Price]', '$_POST[Edition]', now(), '$_POST[hardcover]', 'No')";

works perfectly in another file

Those two work perfectly although I believe they shouldn't

u'll note one uses date() and one uses now() not that it really matters

$SQL_Exec_String = "INSERT INTO Invoice (CustomerID, Date, StatementNo, BookID) VALUES (".$_SESSION['ID'].",now(),  $StatementNo, ".$_SESSION['BookID'].")";
echo $SQL_Exec_String;

wont work

$SQL_Exec_String = "INSERT INTO Invoice (CustomerID, Date, StatementNo, BookID) VALUES (".$_SESSION['ID'].",date(),  $StatementNo, ".$_SESSION['BookID'].")";
echo $SQL_Exec_String;

wont work

although those two are formatted correctly, well according to the first two working files

so I look the date function up

add

$today=date("d/m/y");

$today=date("d/m/y g:i");

$today=date("d/m/y g:i a");

for date

$SQL_Exec_String = "INSERT INTO Invoice (CustomerID, Date, StatementNo, BookID) VALUES (".$_SESSION['ID'].",$today,  $StatementNo, ".$_SESSION['BookID'].")";
echo $SQL_Exec_String;

no matter which one I use, it still gives me a syntax error, I'm connecting to ms access thru odbc and the field is date/time

anyone have any ideas?

matto

$today=date("d/m/y g:i a");
... VALUES (".$_SESSION['ID'].",$today,  ...

put the $today between single quotes, this is an sql requirement.

hope it works then.

Velausanakha

... VALUES (".$_SESSION['ID'].",'$today',  ...

with all three aforementioned options didn't work either 🙁

TTT

$today = date("Y-m-d")

Velausanakha

thats not the format I want the date in and nor does it correspond with the other date formats throughout the other tables :p

However I did re-attempt the process with d-m-y and still no go

I should also say I do echo the query for debugging purposes and the values are correct yet I still get an sql error syntax error

PsychoMark

First of all, the way you've been using now() and date() in the first two functions suggests that these are handles by the database, not PHP. Although variables can be inserted like that into double-quote strings, functions can not.

You say the values look fine, but can you post the output of the echo statement? You might've overlooked something simple or not directly related to the date...

Velausanakha

INSERT INTO Invoice (CustomerID, Date, StatementNo, BookID) VALUES (1,'16-12-03 10:14',  1, 5)<br />
<b>Warning</b>:  SQL error:...etc

is the output to the browser, well from view source

Shrike

A standard SQL datetime field requires the format

yyyy-mm-dd hh:mm:ss

Velausanakha

based on that I did

$today=date('Y-m-d H:i:s');

still didn't work

I now have suspicions about MS Access itself (and its been working beautifully up until here)

Velausanakha

date as a field and function also reserved word in access

smart move on my part huh?

all fixed