Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2008
    Posts
    9

    Red face Unanswered: Datetime field results from prior to specific date and time

    Hi all,

    Please forgive my total lack of DB2 information. I'm actually trying to write a pass-through query in MS Access, but the back end DB server is DB2, so that's why I'm asking here. I have no clue about hardware or version of DB2, but I hope that the basic generality of my question doesn't require that information. I'll say up front that my database experience has been primarily limited to using the Query-by-example grid in MS Access, but I do have some basic understanding of SQL. I think what I'm looking for is the proper SQL syntax for DB2 to return the results I'm seeking.

    Basically I have a table that has a datetime field, and I want to query records that have datetimes earlier than a specific date AND time. I've seen plenty of SQL examples on looking for records prior to a date, but I need to narrow it down to a time, too. Here's my SQL:

    Code:
    SELECT LOGDT
    FROM DV_RECEIVINGLOG
    WHERE LOGDT < #5/1/2009 9:00:00 AM# ;
    I'm getting the following error with this:
    SQL0104N An unexpected token "9" was found following "RE LOGDT > #5/1/2009".
    To me this is saying that there's an issue with using the bangs to delimit the date and time information. I also tried single quotes and get this error instead:
    SQL0180N The syntax of the string representation of a datetime value is incorrect.
    So, can someone point me in the right direction for this? I tried various general SQL tutorials and such thinking that I may find the answer there as a basic SQL mistake, but a lot of what I'm gleaning from those is "don't put your dates and times in the same field, so here's an example for querying dates only..." Unfortunately I don't have the luxury of choosing as this is the database that's running our warehouse and it's already setup this way.

    Thanks ahead of time for helping out this SQL/DB2 beginner...

    Jeremy

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please try:
    WHERE LOGDT < '2009-05-01-09.00.00' ;
    or
    WHERE LOGDT < TIMESTAMP('5/1/2009', '9:00 AM') ;

    Here is an article describing basics of DB2 date and time.....
    DB2 Basics: Fun with Dates and Times

  3. #3
    Join Date
    Mar 2008
    Posts
    9
    Thank you so much, tonkuma! Both of those worked great! Also, thanks for the link.

    Jeremy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •