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?
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.
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!).
Use : TRUNC(Date_Col)
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,
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.
Click Here to Expand Forum to Full Width