Solved

Can't update the datetime files using UPDATE table SET statment.

Posted on 2006-11-28
9
675 Views
Last Modified: 2012-06-21
I have and Access table on which I'm trying to update a columns which is in date/time format.  I am using PERL.  I tried multiple different options but it still does not want to work.  I'm using ODBC to access it.

The PERL code I'm using:
   my $query = "UPDATE tblLibraries SET DateStamp = '$newDateStamp' WHERE LibraryID = '$libraryID'";
   $database->do ($query);

$newDateStamp is in the format "YYYY-MM-DD HH:MM:SS"
$libraryID is just an integer that point to the KEY column.

I'm new in using database.  A week a ago I didn't even know how to build tables.  This might be simple but I can't find a solution to it.  I tried functions like DATEFORMAT, to_date but they don't work.  These factions are probably specific to some other SQL server.

Can one of you help me?

Thanks!


0
Comment
Question by:Ned_LeB
  • 3
  • 3
  • 2
9 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18028644
what about this:
   my $query = "UPDATE tblLibraries SET DateStamp = convert(datetime, '$newDateStamp', 120) WHERE LibraryID = '$libraryID'";
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18028649
>I have and Access table
sorry... as you posted in MS SQL Server (which is something different), I didn't see that...
my above suggestion is for MS SQL Server
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18028653
for access, please try this:
   my $query = "UPDATE tblLibraries SET DateStamp = #$newDateStamp# WHERE LibraryID = '$libraryID'";
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 2

Author Comment

by:Ned_LeB
ID: 18028772
Nop... tried this as well but I'm getting a bit further.

I get the following error:

DBD::ODBC::db do failed: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression. (SQL-22018)(DBD: Execute Immediate failed=-1) at ....

The DBD is the package I use in PERL ot access the database so it's not important.  My problem is the data type mismatch.

Thanks!

Ned
0
 
LVL 3

Expert Comment

by:RickMan2K
ID: 18028807
The answer to this question depend on the date format in the access table.  Most of the functions do not include the time format.  But it should do an automatic conversion if you have the date/time matching the format of the date/time column in the table.  But if you do not kno the format you have a couple of choices that will not pass the time value.

You can use the CDate, DateValue, DateSeriel functions to convert  the string to a date.  The CDate, DateValue Functions expect the date to be in MM-DD-YYYY or Month, DD, YYYY format and dateSerial expects the date to be passed in three parameters Year - YYYY, Month - MM, Day-DD.

Your best bet will be to rearange the format of your $newDateStamp to "MM-DD-YYYY HH:MI:SS"  and change your query to
"UPDATE tblLibraries SET DateStamp = CDate('$newDateStamp') WHERE LibraryID = '$libraryID'";

Usually an access question would be answered in the Microsoft Access  area, not in the SQL Server area.
RickMan2K
0
 
LVL 2

Author Comment

by:Ned_LeB
ID: 18029208
The date format I'm using his Medium date (DD-Month-YY). I tried the following three expressions and I still get a data mismatch error.  

UPDATE tblLibraries SET DateStamp = CDate('$newDateStamp') WHERE LibraryID = '$libraryID'
UPDATE tblLibraries SET DateStamp = CDate(11/19/2003) WHERE LibraryID = '$libraryID'
UPDATE tblLibraries SET DateStamp = CDate('11/19/2003') WHERE LibraryID = '$libraryID'

I also tried the following values for $newDateStamp.
$newDateStamp = "November 11, 2003";
$newDateStamp = "11-19-03";
$newDateStamp = "19-11-03";
$newDateStamp = "11-19-2003";
$newDateStamp = "11-19-2003 HH:MI:SS";
$newDateStamp = "11-19-2003 HH:MI:SS.sss";

Same results.  I'm sure it's something stupid but I can't figure it out.

Thanks!
0
 
LVL 3

Accepted Solution

by:
RickMan2K earned 50 total points
ID: 18029353
What is the format of the DateStamp column in the tblLibraries table.  The format can be found by opening tblLibraries in design mode and selecting the DateStamp column,  next to the field name will be the a datatype value, at the bottom, will be the Field properties.  There are two entries that I would like to know, the actual datatype value, and the format value.

The value in the CDATE function must be a string, and either be surrounded by "" or ##  so try this as well -
UPDATE tblLibraries SET DateStamp = CDate(#'$newDateStamp'#) WHERE LibraryID = '$libraryID'

RickMan2K
0
 
LVL 2

Author Comment

by:Ned_LeB
ID: 18029911
It worked

my $query = "UPDATE tblLibraries SET DateStamp = Cdate(\#$newDateStamp\#) WHERE LibraryID = $libraryID";

It was a combinations of problems.  The quotes around the $libraryID caused part of the pRoblem since the printed statement looked like:

UPDATE tblLibraries SET DateStamp = Cdate(#2003-11-19 09:09:09#) WHERE LibraryID = '1234'

removing the quote I got:

UPDATE tblLibraries SET DateStamp = Cdate(#2003-11-19 09:09:09#) WHERE LibraryID = 1234

which worked ok.

Thanks for your help.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question