CODEWITHSUNDEEP
phpdatems-accesspdo
bikey77
bikey77

Reputation: 6672

PHP and date formatting in access mdb

I'm using PHP to insert data to an MS Access DB (*.mdb). The field CreateDate is of type Date/Time and the data that gets inserted is in the wrong format. This is my query:

$date = date('d/m/Y');
$dbh = new PDO("odbc:Driver={Microsoft Access Driver (*.mdb)}; Dbq="mydb.mdb"; Uid=Admin");     
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$dbh->exec("INSERT INTO Cup_Package (Cup_BorgPartnerID, Name, CreateDate)           VALUES (1, 'Catalog and Price Update', $date)");

but instead of writing a date like 2/9/2013, the date field gets 30/12/1899 which probably means either that the formatting i'm giving is incorrect or that the date field is getting no data at all. Any ideas?

Upvotes: 1

Views: 2338

Answers (1)

Renaud Bompuis
Renaud Bompuis

Reputation: 16786

In Access, date literals must be enclosed in # and are formatted as such:

#MM/DD/YYYY#

or, for a Date and Time:

#MM/DD/YYYY HH:MM:SS#

Access also accept date literals as decimal numbers where the integer part is the number of days and the decimal part is the fraction of a day.

In your case though:

$date = date('m/d/Y');
$dbh->exec("INSERT INTO Cup_Package (Cup_BorgPartnerID, Name, CreateDate) 
           VALUES (1, 'Catalog and Price Update', #$date#)");

Upvotes: 3

Related Questions