Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2006-11-28
9
Medium Priority
?
692 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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 200 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

722 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