The # is specific to access, and in fact will not work with SQL. You can update the field to
 null if the field in nullable in order to clear a date, though.

What I would usually do to accomplish this is use a prepared statement something like this (
assuming $dbh is a valid database handle:

My $sql = $dbh-> prepare(q{ mytable set mydate=null where mydate = ?}) || die "Can't prepare:
 $DBI::errstr";
$sql-> execute('2008-03-23') || die "Can't execute: $DBI::errstr";


Or to update to clear it (null it) you could change the execute line to this:

$sql-> execute(undef) || die "Can't execute: $DBI::errstr";

Steve Howard

-----Original Message-----
From: perl-win32-database-bounces@[...].com [mailto:perl-win32-database-bounces@[...].com] O
n Behalf Of Shaun Wallace
Sent: Monday, March 24, 2008 9:47 AM
To: Paul Davison; perl-win32-database@[...].com
Subject: RE: Clearing the contents of a Datetime field

I have not used ODBC to write dates to Access, however I know that when
querying and updating dates in Access you need to surround dates with
#'s such as #2008-03-24 12:30:26 PM#.  Also to "clear" a date with
Access you simply update the date field to Null.  I assume you would use
the same idea with ODBC.

See if this works via ODBC:

$sql = "update mytable set mydate=null where mydate=#2008-03-23#"

Thanks,
Shaun

________________________________

Moore & Van Allen <http://www.mvalaw.com/> 

Shaun K. Wallace
Data Analyst / Paralegal

Suite 4700
100 North Tryon Street
Charlotte, NC  28202-4003






________________________________

        From: perl-win32-database-bounces@[...].com
[mailto:perl-win32-database-bounces@[...].com] On Behalf
Of Paul Davison
        Sent: Monday, March 24, 2008 12:14 PM
        To: perl-win32-database@[...].com
        Subject: Clearing the contents of a Datetime field


        I am using ODBC to work on data in an Access database and need
to find a way to clear a date/time field. I have tried writing
"0000-00-00 00:00:00" and "" into it and I get back Error: [-3030] [1]
[0] "[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in
criteria expression."

        Is there a method to clear the contents of a Date/time field or
set it to a null value?

        Paul


--------------------------------------------------------------------------
To comply with certain U.S. Treasury regulations, we inform you that, unless expressly state
d otherwise, any U.S. Federal tax advice contained in this e-mail, including attachments, is
 not intended or written to be used, and cannot be used, by any person for the purpose of av
oiding any penalties that may be imposed by the Internal Revenue Service.
--------------------------------------------------------------------------
CONFIDENTIAL & PRIVILEGED
Unless otherwise indicated or obvious from the nature of the following communication, the in
formation contained herein is attorney-client privileged and confidential information/work p
roduct. The communication is intended for the use of the individual or entity named above. I
f the reader of this transmission is not the intended recipient, you are hereby notified tha
t any dissemination, distribution or copying of this communication is strictly prohibited. I
f you have received this communication in error or are not sure whether it is privileged, pl
ease immediately notify us by return e-mail and destroy any copies, electronic, paper or oth
erwise, which you may have of this communication.
--------------------------------------------------------------------------

_______________________________________________
Perl-Win32-Database mailing list
Perl-Win32-Database@[...].com
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
_______________________________________________
Perl-Win32-Database mailing list
Perl-Win32-Database@[...].com
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs