sql date problems
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: sql date problems

Hybrid View

  1. #1
    Join Date
    Aug 2000
    Posts
    143
    I'm having trouble with a query:
    select * from user_details where regdate = '08-NOV-00'
    it returns nothing. There are entries in this table for this date. Has anyone had a similar problem?

  2. #2
    Join Date
    Oct 2000
    Posts
    123
    This is bcoz the date type and char type is not the same thing.
    When we insert date into the table, it is not only that date being inserted, the time is inserted as well. So, U need use the conversion function "to_char" against the date column to make a query based on Ur specific condition, somthing like:
    " where to_char(data_col) = 'specific date' " in the query. Sure, there are other ways, but they are generally based on the date features.

    Take care

  3. #3
    Join Date
    Nov 1999
    Location
    Elbert, Colorado, USA
    Posts
    81
    Check to see what your setting for NLS_DATE_FORMAT is. By default, it is 'DD-MON-RR' or 'DD-MON-RR' (depending on the version you are using). If this is the case, then your query should have worked. However, if you have a different setting (e.g., 'Month DD, RRRR'), then you have to either rewrite your query to use this format or use the to_char function to specify your desired format.

    You might want to do something like
    SELECT DISTINCT regdate FROM user_details;
    to see if there is indeed an entry with the date you are providing in your query. It's possible that there are no rows that match your criteria (in which case, No Rows Returned is exactly right!).

  4. #4
    Join Date
    Sep 2000
    Posts
    47

    Use : TRUNC(Date_Col)

    Hi,

    Using Date_Col = '08-Nov-00' will also include Time part, HH:MI:SS in the Date_Col, at the time of comparision. So try out truncating the column. Try : TRUNC(Date_Col) = '08-Nov-00' and do check the NLS Date formate for your database.

    Hope this works,

    Pinakin.

  5. #5
    Join Date
    Nov 1999
    Location
    Elbert, Colorado, USA
    Posts
    81
    Excellent point!

    When you say
    WHERE date_col = '8-Nov-00'
    Oracle interprets that as 00:00:00 on 8-Nov-00. If you have a row that was inserted with a date/time group of 01:05 on 8-Nov-00, the row will not be selected. Pinakin's modification takes care of this problem.

Posting Permissions

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


Click Here to Expand Forum to Full Width