Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2010
    Posts
    15

    Unanswered: I misused between, now I need to fix it

    I have a table with two fields, obsuid, an int, and obscreatetime, a datetime. I ran this query and exported it and began working with the data.
    Code:
    select obsuid from observation where obscreatetime between '2015-02-20 00:00:00'  and '2015-02-20 23:59:59'
    Later I realized I was missing some data. Some of the data came in at exactly 23:59:59. I didn't realize BETWEEN didn't include the two date/times I specified (guess I should've done >= or something).

    So I've exported a lot of data and done a lot of work on it. I'm not too keen with running a bunch of queries and reformatting the data. Is there any way I can run the above query based on time, regardless of date? So I want to query for all the obsuid values when the time is either 00:00:00 or 23:59:59.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Your problem is that you have timestamps that are > 23:59:59 because timestamps have fractional seconds. When you write a timestamp without fractional seconds like you did, then IBM assumes that they are 0. IBM gives you a way out of your situation. Use 24:00:00.

    This works for LUW anyway.

    Andy

Posting Permissions

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