Select from table by knowing only date without time ORACLE

Question

I m trying to retrieve records from table by knowing the date in column contains date and time   Suppose I have table called t1 which contains only two column name and date respectively   The data stored in column date like this 8 3 2010 12 34 20 PM   I want to retrieve this record by this query for example  note I don t put the time    Select   From t1 Where date  8 3 2010    This query give me nothing     How can I retrieve date by knowing only date without the time

User · Answer

You could use the between function to get all records between 2010-08-03 00 00 00 000 AND 2010-08-03 23 59 59 000

User · Answer

Try the following way    Select   from t1 where date col name   8 3 2010

User · Answer

Personally  I usually go with   select    from   t1 where  date between trunc   somedate            -- 00 00 00             and     trunc   somedate      99999 -- 23 59 59

User · Answer

Convert your date column to the correct format and compare   SELECT   From my table WHERE to char my table my date col  MM dd yyyy      8 3 2010    This part  to char my table my date col  MM dd yyyy     Will result in string  8 3 2010

User · Answer

Simply use this one   select   from t1 where to date date column   8 3 2010

User · Answer

DATE is a reserved keyword in Oracle  so I m using column-name your date instead   If you have an index on your date  I would use  WHERE your date  gt   TO DATE  2010-08-03    YYYY-MM-DD     AND your date  lt   TO DATE  2010-08-04    YYYY-MM-DD     or BETWEEN   WHERE your date BETWEEN TO DATE  2010-08-03    YYYY-MM-DD                       AND TO DATE  2010-08-03 23 59 59    YYYY-MM-DD HH24 MI SS     If there is no index or if there are not too many records  WHERE TRUNC your date    TO DATE  2010-08-03    YYYY-MM-DD     should be sufficient  TRUNC without parameter removes hours  minutes and seconds from a DATE     If performance really matters  consider putting a Function Based Index on that column   CREATE INDEX trunc date idx ON t1 TRUNC your date

User · Answer

trunc my date  DD   will give you just the date and not the time in Oracle

[sql] Select from table by knowing only date without time (ORACLE)

Examples related to sql

Examples related to oracle

Examples related to date