inserting a date into an access DB

bdee1

i am trying to insert a simple date into an access database using php.

my code looks liek this:

$today = date('m/d/Y');

$conn=odbc_connect('DVDLobby','','');
$sql="INSERT INTO tblDVD (PurchaseDate)
VALUES($today)"; 
$rs=odbc_exec($conn,$sql);

and it works but when i open up the db in access and look at the record that was inserted, thePurchaseDate field is 12:00:22 AM instead of 01/06/2006.

is there some other function i need to use to format it as an odbc date or something?

AIS4U

bdee1:

I'm not sure, but since your PHP code looks fine I would check my database in design view and try setting the purchasedate field to "TEXT" instead of Date/Time.

Not sure if this will work, but I think so.

Good Luck

bdee1

unfortunately modifying the database design is not an option because the database is part of another commercial application. if i change the database, the other app will probably break.

AIS4U

bdee1:

OK. Well, what about trying the suggestion that was posted in response to your post about query? By that I mean try using 2 single quotes around your: date('m/d/Y');
Change this to date(''m/d/Y''); That is 2 single quotes, not one double quote.

bdee1

i also tried the following formats and they had unexpected results.

1-06-2006
2006-1-06

with these formats they showed up in the database as 6/23/1894

thoughts?

bdee1

AIS4U wrote:
bdee1:

OK. Well, what about trying the suggestion that was posted in response to your post about query? By that I mean try using 2 single quotes around your: date('m/d/Y');
Change this to date(''m/d/Y''); That is 2 single quotes, not one double quote.

ok so in my original sql statement i didnt have any quotes around the date variable (i thought that date fields didnt requirte the singel quotes in SQL).

so i tried putting a single set of single quotes and now it seems to be working.

thanks for all yoru help today everyone. the more of this stuff i do the more i am learning.