aspose file tools*
The moose likes JDBC and the fly likes Having trouble using Dates in SQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Having trouble using Dates in SQL" Watch "Having trouble using Dates in SQL" New topic
Author

Having trouble using Dates in SQL

Corey McGlone
Ranch Hand

Joined: Dec 20, 2001
Posts: 3271
Let me try to describe my situation. I need to perform an update on a record in an Access database and I need to pick which record to update by two criteria, one of which is a Date field (in Access, the actual datatype of the field is Date/Time).
Here is what I currently have:

However, whenever I try to execute that code, I get the following exception:

So does anyone know how I can get around this? I need to use that date/time as the criteria for the record I'm after (because contractNum might not be unique but contractNum + requestReceived will be) but I can't seem to get that to work. Any ideas?
Thanks, folks.
Corey
[ April 22, 2004: Message edited by: Corey McGlone ]

SCJP Tipline, etc.
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17257
    
    6

Yes, I do.
You see Access and its Date/Time datatype is a pain in the arse. I had this identical problem with ODBC and VB 6.0
here is my solution. In the query you need to format the Date/Time field into a String and then you can have it in your WHERE clause, as in
Format$(NetWorths.EntryDate,'MM/DD/YYYY')
So instead of NetWorths.EntryDate, you will put in your actual table.fieldname. This will format the date/time into just the date as a string
So in your WHERE clause put something like
WHERE
Format$(NetWorths.EntryDate,'MM/DD/YYYY') = '04/22/2004'
Good Luck
Mark


Perfect World Programming, LLC - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61428
    
  67

I'd also investiage the use of PreparedStatement which will eliminate translation and delimiting issues.


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Eric Pascarello
author
Rancher

Joined: Nov 08, 2001
Posts: 15376
    
    6
My 2 cents is that you should try using a Date instead of a String, but I do not know Java!
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17257
    
    6

So your code re-written would be

Hope that helps.
Mark
[ April 22, 2004: Message edited by: Mark Spritzler ]
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61428
    
  67

Eric may not know Java, but his instincts are on-target. With a PreparedStatement you set the data as a Date and don't perform a String translation. Why not let the driver do the work (and do it properly) for you?
[ April 22, 2004: Message edited by: Bear Bibeault ]
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17257
    
    6

Originally posted by Bear Bibeault:
Eric may not know Java, but his instincts are on-target. With a PreparedStatement you set the data as a Date and don't perform a String translation. Why not let the driver so the work (and do it properly) for you?

Unfortunately the problem isn't with Java, it is with Access and how to do queries against its tables and a Date/Time datatype.
Mark
Corey McGlone
Ranch Hand

Joined: Dec 20, 2001
Posts: 3271
Originally posted by Bear Bibeault:
I'd also investiage the use of PreparedStatement which will eliminate translation and delimiting issues.

I should have pointed this out originally, I suppose, but I had actually already tried that. I was hoping to do just what you were thinking - avoid the issue, entirely. Unfortunately, when I tried that, I got this:

Anyway, I'm going to try Mark's suggestion and see if I can't get past my error. Thanks for the help, everyone.
Corey
Eric Pascarello
author
Rancher

Joined: Nov 08, 2001
Posts: 15376
    
    6
The reason I said it is that I passing a string to my stored proceedure with .NET and a SQL Server 2000 database and it was erroring out saying that the data types were a mis-match so I forced it to be a date on the .NET side and it cleared my error.
Man that is one long sentence there...
Corey McGlone
Ranch Hand

Joined: Dec 20, 2001
Posts: 3271
Well, Mark's suggestion got me a little further, but it's still not quite working. I'm not getting the error message now, so it appears that it is successfully comparing the Dates now. Unfortunately, I can't get it to match the record I want in the database to perform the update. The extra crummy part is that now I don't get an error message, but I also can't really see what's going wrong, either - it just says that there were no rows to update by returning 0 from the executeUpdate method.
One thing I probably should have explicity pointed out earlier is that the database is storing a Date as well as a Time. For example, an entry in the database might look like this:
2/19/2004 11:36:29 AM
I tried taking Mark's suggestion a little further (along with the MS Access Help files) and came up with this line:

Unfortunately, I still can't get it to match the record I want. The worst part about it is that I don't know what the "FORMAT$" function is really doing to my String to make it match the date in Access. Is there any way that I could see the result of that function? Maybe I can do a little VBA script in Access to pop that up in a message box or something... I'll try that out tomorrow when I'm back at work.
Anyway, I just thought I'd let you know where I sit right now. If I can't get this figured out soon, I might just turn those Date/Time fields into String fields - I'm just not getting much benefit from using the specialized datatype and it's giving me a big headache.
Thanks for the help so far, folks,
Corey
[ April 22, 2004: Message edited by: Corey McGlone ]
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17257
    
    6

If you intend to use the Time portion in your query, you will find it very very difficult to get a match. I suggest using just the date portion, unless there will be two records for the same ID and date, then you will have to use the Time, but maybe not so much of a precision.
The query has to be precise, or you will be off. Even for a second.
Mark
[ April 22, 2004: Message edited by: Mark Spritzler ]
Eddie Vanda
Ranch Hand

Joined: Mar 18, 2003
Posts: 281
I read somewhere that dates in access are stored as floating point numbers with the whole number being the date and the fraction being the time. A fraction of .5 would be noon.
A property of fractions, based on binary bits, is that they cannot always represent every possible decimal number so rather than matching for equality you should match against an earlier and later date/time to be sure to get your match.
I now use integer numbers to represent dates: eg 20040423 for today and that seems to work ok for me. I store time in a separate minute of the day field also as an integer.
If your db is already set up you should think about range checking to get your date.


The nice thing about Standards is that there are so many to choose from!
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Having trouble using Dates in SQL