Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2006-11-28
9
Medium Priority
?
697 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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 …

972 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