Solved

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

Posted on 2006-11-28
9
670 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 142

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 142

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 142

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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Use a Combo-Box to do a Search 26 23
Reference theme colors in VBA 4 23
Run SQL Server Proc from Access 11 27
Access check if a table is open 4 38
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

813 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now