Hi,

Does anyone have any idea what is up with Microsoft Access and its 
ridiculous Date/Time formatting options? It doesn't seem to matter what 
format I attempt to insert with. The directory and db file permissions 
are all Full Control. I'm using the "#" around the Date/Time format as 
required. The database has nothing as the Format in Design view on the 
General tab. The insert below has never worked once. On the up side if I 
run an insert that has no mention of the Date/Time field then it will 
just insert the record using the =Now() function as the default value 
but that doesn't help much considering I would want to update the field. 
There are no question marks in any of the field names as suggested on 
many pages dicussing this error below. Thanks for any help!  I'm using 
MS Access, Perl, DBI & DBD::ODBC win32 modules, IIS on XP.

Here is the error:
........................................................................
DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access 
Driver]COUNT field incorrect  (SQL-07002)(DBD: st_execute/SQLExecute 
err=-1) at C:\Inetpub\Scripts\somedir\dsntest.pl line 18.
........................................................................


Here is the code:
........................................................................
#!C:\perl\bin\perl.exe
#
#print "Content-type: text/html\n\n";
use DBI;
#         2005-04-12 20:48:31
#         4/5/2004 5:3:45 PM
#         4/12/2005 10:28:14 PM
#         d m yyyy h:m:s AM/PM

$dt = "#4/5/2004 5:3:45 PM#";

$sql = "INSERT INTO tblTest (TimeStamp) VALUES ($dt)";

my $dbh = DBI->connect("dbi:ODBC:dsntest", "", "", { PrintError => 0, 
RaiseError => 1 })
          || die "database connection can't connect to database 
$DBI::errstr";

my $sth = $dbh->prepare($sql) ||  die "prepare statement- $DBI::errstr";
$sth->execute() || die "execute statement- $DBI::errstr";
$sth->finish;
........................................................................

On Tue, 12 Apr 2005 23:34:09 -0700, Robb wrote:

>Does anyone have any idea what is up with Microsoft Access and its 
>ridiculous Date/Time formatting options? 

I am guessing that MS-Access formats dates according to your settings in
the "Regional Settings" control panel.

-- 
	Bart.

On Tue, 2005-04-12 at 23:34 -0700, Robb wrote:
> Does anyone have any idea what is up with Microsoft Access and its 
> ridiculous Date/Time formatting options?

I asked that question daily in a previous life working with access, and
that that was with ms tools! I never got a sane answer.
I don't now if newer versions are any better but access 97 and 2000 both
had a nasty habit of sometimes ignoring any regional settings and just
using American dates.

As Bart suggested checking the regional settings for the box is worth
it. I would also check how the dsn is setup.

> $dt = "#4/5/2004 5:3:45 PM#";
> 
> $sql = "INSERT INTO tblTest (TimeStamp) VALUES ($dt)";
> 
> my $dbh = DBI->connect("dbi:ODBC:dsntest", "", "", { PrintError => 0, 
> RaiseError => 1 })
>           || die "database connection can't connect to database 
> $DBI::errstr";
> 
> my $sth = $dbh->prepare($sql) ||  die "prepare statement- $DBI::errstr";
> $sth->execute() || die "execute statement- $DBI::errstr";
> $sth->finish;
> .......................................................................

Do you get the same error if you use a bind value instead of putting $dt
directly in the string? ie

 $sql = "INSERT INTO tblTest (TimeStamp) VALUES (?)";
 $sth->execute($dt) || die "execute statement- $DBI::errstr";

Also have you tried ODBC date formats as your going through a DSN?

mark
--


This email (and any attachments) is intended solely for the individual(s) to whom addressed. 
It may contain confidential and/or legally privileged information. 
Any statement or opinions therein are not necessarily those of ITN unless specifically stated. 
Any unauthorised use, disclosure or copying is prohibited. 
If you have received this email in error, please notify the sender and delete it from your system. 
Security and reliability of the e-mail and attachments are not guaranteed. 
You must take full responsibility for virus checking.



Independent Television News Limited, 

Registered No. 548648 England,

VAT Reg. No: GB 756 2995 81, 

200 Gray's Inn Road, London WC1X 8XZ,

Telephone: 020 7833 3000.

Robb wrote:

> Hi,
>
> Does anyone have any idea what is up with Microsoft Access and its
> ridiculous Date/Time formatting options?

:)

In VB, I use the format:

#dd-mmm-yyyy HH:mm:ss#

Your example:
$dt = "#4/5/2004 5:3:45 PM#";

I would write as:
$dt = "#04-MAY-2004 17:03:34#";

You have to pad your numbers. You can't just use "5:3:45" and expect
Access ( or others ) to know what you want.
Converting the month to a 3-character string version forces Access to
recognise the 1st part as the day and the last part as the year. If you
don't do this, it's selection of day/month/year positions appears to be
random.

-- 
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: dkasak@nusconsulting.com.au
website: http://www.nusconsulting.com.au

On Thu, Apr 14, 2005 at 09:19:00AM +1000, Daniel Kasak wrote:
> Robb wrote:
> 
> > Hi,
> >
> > Does anyone have any idea what is up with Microsoft Access and its
> > ridiculous Date/Time formatting options?
> 
> :)
> 
> In VB, I use the format:
> 
> #dd-mmm-yyyy HH:mm:ss#
> 
> Your example:
> $dt = "#4/5/2004 5:3:45 PM#";
> 
> I would write as:
> $dt = "#04-MAY-2004 17:03:34#";

Wouldn't single quotes also work?:

  $dt = "'04-MAY-2004 17:03:34'";

> Converting the month to a 3-character string version forces Access to
> recognise the 1st part as the day and the last part as the year. If you
> don't do this, it's selection of day/month/year positions appears to be
> random.

Doesn't putting the year first force YYYY-MM-DD interpretation?

Tim [who doesn't know Access (and doesn't want to) but is curious]

>=20
>=20
> On Thu, Apr 14, 2005 at 09:19:00AM +1000, Daniel Kasak wrote:
> > Robb wrote:
> >=20
> > > Hi,
> > >
> > > Does anyone have any idea what is up with Microsoft=20
> Access and its=20
> > > ridiculous Date/Time formatting options?
> >=20
> > :)
> >=20
> > In VB, I use the format:
> >=20
> > #dd-mmm-yyyy HH:mm:ss#
> >=20
> > Your example:
> > $dt =3D "#4/5/2004 5:3:45 PM#";
> >=20
> > I would write as:
> > $dt =3D "#04-MAY-2004 17:03:34#";
>=20
> Wouldn't single quotes also work?:
>=20
>   $dt =3D "'04-MAY-2004 17:03:34'";
>=20
> > Converting the month to a 3-character string version forces=20
> Access to=20
> > recognise the 1st part as the day and the last part as the year. If=20
> > you don't do this, it's selection of day/month/year=20
> positions appears=20
> > to be random.
>=20
> Doesn't putting the year first force YYYY-MM-DD interpretation?
>=20
> Tim [who doesn't know Access (and doesn't want to) but is curious]

Sorry for responding late.  The best way with DBI & ODBC is to use the =
ODBC date and time
formats, which are in the DBD::ODBC tests.  Perhaps it should be in the =
documentation too
....  The short version is to use ODBC escapes:
	{d 'YYYY-MM-DD'}
Or
	{ts 'YYYY-MM-DD HH:MM:SS'}

This works on most ODBC drivers correctly, even when binding parameters, =
although I recall
that I had issues with Oracle drivers at one point.

Note also that since you are using DBI and ODBC, you are in effect =
bypassing some of the
Access front end specifics.

Regards,

Jeff

>=20

Tim Bunce wrote:

>On Thu, Apr 14, 2005 at 09:19:00AM +1000, Daniel Kasak wrote:
>  
>
>>Robb wrote:
>>
>>    
>>
>>>Hi,
>>>
>>>Does anyone have any idea what is up with Microsoft Access and its
>>>ridiculous Date/Time formatting options?
>>>      
>>>
>>:)
>>
>>In VB, I use the format:
>>
>>#dd-mmm-yyyy HH:mm:ss#
>>
>>Your example:
>>$dt = "#4/5/2004 5:3:45 PM#";
>>
>>I would write as:
>>$dt = "#04-MAY-2004 17:03:34#";
>>    
>>
>
>Wouldn't single quotes also work?:
>
>  $dt = "'04-MAY-2004 17:03:34'";
>
>  
>
It depends on what your drivers are doing to the data before it gets to
Access. Access by itself certainly won't allow single quotes for dates.
I haven't used Perl to talk to Access before. I've seen it done with
DBD::Proxy or something like that, but I've never tried to store
anything useful in Access.

>>Converting the month to a 3-character string version forces Access to
>>recognise the 1st part as the day and the last part as the year. If you
>>don't do this, it's selection of day/month/year positions appears to be
>>random.
>>    
>>
>
>Doesn't putting the year first force YYYY-MM-DD interpretation?
>
>  
>
Ha! You'll never get a Microsoft product to reliably recognise that date
format. For a while I thought I'd convinced SQL Server to understand it,
but I must have been mistaken ... it will understand the year bit
alright, but it swaps the month and day around if the day is less than 13.

>Tim [who doesn't know Access (and doesn't want to) but is curious]
>  
>
Tim, if I were you, I'd keep it like that ( not knowing Access ). Move
your data to MySQL or something else that at least follows standards and
doesn't explode when you feed it more than 100 rows.

-- 
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: dkasak@nusconsulting.com.au
website: http://www.nusconsulting.com.au

>=20
> On Thu, Apr 14, 2005 at 07:50:43AM -0400, Jeff Urlwin wrote:
> > > >=20
> > > > I would write as:
> > > > $dt =3D "#04-MAY-2004 17:03:34#";
> > >=20
> > > Wouldn't single quotes also work?:
> > >=20
> > >   $dt =3D "'04-MAY-2004 17:03:34'";
> > >=20
> > > > Converting the month to a 3-character string version forces
> > > Access to
> > > > recognise the 1st part as the day and the last part as=20
> the year.=20
> > > > If
> > > > you don't do this, it's selection of day/month/year=20
> > > positions appears
> > > > to be random.
> > >=20
> > > Doesn't putting the year first force YYYY-MM-DD interpretation?
> > >=20
> > > Tim [who doesn't know Access (and doesn't want to) but is curious]
> >=20
> > Sorry for responding late.  The best way with DBI & ODBC is=20
> to use the=20
> > ODBC date and time formats, which are in the DBD::ODBC=20
> tests.  Perhaps=20
> > it should be in the documentation too ...  The short=20
> version is to use ODBC escapes:
> > 	{d 'YYYY-MM-DD'}
> > 	{ts 'YYYY-MM-DD HH:MM:SS'}
>=20
> Thanks Jeff. But do you know the answers to the two questions?

In terms of Access, I don't really know what it will and won't do in =
"ODBC" mode, rather
than "JET" mode.  Access has two (ugly) heads and DBI/DBD::ODBC only tap =
into the ODBC
side, while the Access interface side has different rules/behavior (you =
can create certain
column types in the engine that you can't in ODBC, as far as I can =
tell). =20

In the strictest ODBC sense, no, it doesn't seem to work that way...in =
either case.  THE
thing to do is escape the dates and times as per above.  That will also =
ensure some level
of compatibility across database systems.  Typically, I find that you =
get compatibility
with the host DBMS, but Access is different.  The "jet" / front end side =
does things
differently than the ODBC "front end" -- it's just two heads accessing =
the same tail
(again, as far as I can tell).

Jeff

>=20
> Tim.
>=20

Well Thanks for all the feedback.  I thought I had figured it out using 
a combination of various pieces from everyone who posted to the 
questions. But again, it wasn't working ... I had one script working and 
another that wasn't ... what the !@#$. Anyway,  "I think" I've figured 
out what the problem is.  Ya, its annoying as hell.  At this point I'm 
assuming that the whole "#" thing for Date/Time fields is really for 
Micorosoft centric technologies such as ASP.  But for Perl &  DBI/DBD 
used with Access the problem was seemingly bizarre because I had 
multiple problems.  The cdate('$dt') function works .... and so does 
this other format method {ts '2010-10-11 07:07:07'} ... but also does 
simple single quotes as Tim suggested.  As it turns out what was really 
nailing me is that the field name has a lot to do with it.  I originally 
had the Date/Time field named "TimeStamp" which doesn't work, I have 
also tried DateTime which doesn't work, dt worked which was why my 
simple prototype worked while my app didn't, Date_Time works .... so 
apparently I can't pick a decent field name.  Funny thing is, this was 
someone else's database I usually don't use Upper Case at all.  So, I'm 
going to rename all these TimeStamp fields and hope for the best.  I 
hope the next project is a MySQL one!  Any additional info would be 
appreciated immensely.

$dt = "4/14/2005 23:10:57 PM";
$sql = "UPDATE tblTest SET name = 'Robb', dt = cdate('$dt') WHERE id = 1";

$sql = "UPDATE tblTest SET name = 'Robb', Date_Time = {ts '2010-10-11 
07:07:07'} WHERE id = 1";

my $sql = "UPDATE tblUser SET EmailAddress = '$newemail', Date_Time = 
'$dt' " .
              "WHERE Password = '$password'";


TimeStamp < ---- BAD
DateTime <---- BAD


Robb

Robb wrote:
> As it turns out what was really 
> nailing me is that the field name has a lot to do with it.

[snip]

> TimeStamp < ---- BAD
> DateTime <---- BAD

Not to encourage any further deviation from standards, but with Access and SQLServer databases you can still use reserved words as table or 
column names, as long as you qoute them properly.
With MS databases, you have to surround those names in square brackets:

	select [TimeStamp] from [DateTime]

Not recommended though.

Rhesa