Thread: Comparing Dates

    #1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Melbourne
    Posts
    3
    Rep Power
    0

    Comparing Dates


    I am trying to match exact dates in an SQL query in Oracle. Can someone please tell me why this query works:

    SELECT count(*)
    FROM sessions
    where sess_date > to_date('2003-09-03', 'YYYY-MM-DD');

    but this query does not:

    SELECT count(*)
    FROM sessions
    where sess_date = to_date('2003-09-03', 'YYYY-MM-DD');

    when I know the date does exist in the database. It's driving me mad.
  2. #2
  3. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Austin, TX
    Posts
    9
    Rep Power
    0
    I suspect that you would have better luck using:

    select count(*)
    from sessions
    where to_char(sess_date, 'YYYY-MM-DD') = '2003-09-03';

    As for why your second query doesn't work... well, the most likely problem is that sess_date has a time component that's not set to exactly midnight. I'd suggest using to_char rather than to_date, though, as it gives you a little better control over exactly what you're trying to compare.

    Marty
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Melbourne
    Posts
    3
    Rep Power
    0

    Thanks


    It worked!

    Thanks for your help martysb.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    68
    Rep Power
    12
    I usually do it via:

    SELECT count(*)
    FROM sessions
    where trunc(sess_date) = to_date('2003-09-03', 'YYYY-MM-DD');

    Like this we compare date fields and not character fields as martysb showed us.

    Cheers,
    Dan
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Melbourne
    Posts
    3
    Rep Power
    0

    Thanks


    Thanks Dan

    That's great

IMN logo majestic logo threadwatch logo seochat tools logo