• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Having trouble using Dates in SQL

 
Ranch Hand
Posts: 3271
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
 
ranger
Posts: 17347
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Sheriff
Posts: 67746
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I'd also investiage the use of PreparedStatement which will eliminate translation and delimiting issues.
 
author
Posts: 15385
6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
My 2 cents is that you should try using a Date instead of a String, but I do not know Java!
 
Mark Spritzler
ranger
Posts: 17347
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
So your code re-written would be

Hope that helps.
Mark
[ April 22, 2004: Message edited by: Mark Spritzler ]
 
Bear Bibeault
Sheriff
Posts: 67746
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 17347
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 3271
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 15385
6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 3271
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 17347
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
 
Ranch Hand
Posts: 283
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
reply
    Bookmark Topic Watch Topic
  • New Topic