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-bounceslistserv.ActiveState.com
[mailto:perl-win32-database-bounceslistserv.ActiveState.com]
On Behalf
Of Paul Davison
	Sent: Monday, March 24, 2008 12:14 PM
	To: perl-win32-databaselistserv.ActiveState.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 stated 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 avoiding
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 information contained herein is
attorney-client privileged and confidential information/work
product. The communication is intended for the use of the
individual or entity named above. If the reader of this
transmission is not the intended recipient, you are hereby
notified that any dissemination, distribution or copying of
this communication is strictly prohibited. If you have
received this communication in error or are not sure whether
it is privileged, please immediately notify us by return
e-mail and destroy any copies, electronic, paper or
otherwise, which you may have of this communication.
------------------------------------------------------------
--------------

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

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-bounceslistserv.ActiveState.com
[mailto:perl-win32-database-bounceslistserv.ActiveState.com]
On Behalf Of Shaun Wallace
Sent: Monday, March 24, 2008 9:47 AM
To: Paul Davison; perl-win32-databaselistserv.ActiveState.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-bounceslistserv.ActiveState.com
[mailto:perl-win32-database-bounceslistserv.ActiveState.com]
On Behalf
Of Paul Davison
        Sent: Monday, March 24, 2008 12:14 PM
        To: perl-win32-databaselistserv.ActiveState.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 stated 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 avoiding
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 information contained herein is
attorney-client privileged and confidential information/work
product. The communication is intended for the use of the
individual or entity named above. If the reader of this
transmission is not the intended recipient, you are hereby
notified that any dissemination, distribution or copying of
this communication is strictly prohibited. If you have
received this communication in error or are not sure whether
it is privileged, please immediately notify us by return
e-mail and destroy any copies, electronic, paper or
otherwise, which you may have of this communication.
------------------------------------------------------------
--------------

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