Solved

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

Posted on 2006-11-28
9
686 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

738 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