CODEWITHSUNDEEP
sqloracle-databaseoracle10g
Hajime
Hajime

Reputation: 11

Oracle 10g select query by date returning no result

I have some confusing problem.

 SQL> select login, status, date_modified from users where login = 'zack_661' and date_modified = '05-MAR-14';

no rows selected

SQL> select login, status, date_modified from users where login = 'zack_661' and date_modified = sysdate;

no rows selected

There is no result for both queries but if i'm not specifying date then it returned with result. 

SQL> select login, status, date_modified from users where login = 'zack_661';

LOGIN                                                  STATUS DATE_MODI
-------------------------------------------------- ---------- ---------
zack_661                                                    1 05-MAR-14

SQL> select sysdate from dual;

SYSDATE
---------
05-MAR-14

and here is the table schema. 

SQL> desc users;

Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
LOGIN                                     NOT NULL VARCHAR2(50)
STATUS                                             NUMBER
DATE_CREATED                                       DATE
DATE_MODIFIED                                      DATE

i'm also trying using other function but still no result

SQL> select login, status, date_modified from users where login = 'zack_661' and date_modified = to_date('05-MAR-14','DD-MON-YY');

no rows selected

SQL> select to_date('05-MAR-14','DD-MON-YY') from dual;

TO_DATE('
---------
05-MAR-14

Thanks for your help in advance.

Updated

Thanks to KevinKirkpatrick. I can conclude like this. 

SQL> select login, status, date_modified from users where login = 'zack_661' and date_modified between TO_DATE('05-MAR-2014 00:00:00', 'DD-MON-YYYY HH24:MI:SS') and TO_DATE('05-MAR-2014 23:59:59', 'DD-MON-YYYY HH24:MI:SS');

LOGIN                                                  STATUS DATE_MODI
-------------------------------------------------- ---------- ---------
zack_661                                                    1 05-MAR-14

For anyone else thanks a lot for your help. I'm really appreciate it. I should help others to since i'm new here.

Upvotes: 1

Views: 2739

Answers (4)

Amit Arora
Amit Arora

Reputation: 31

If you don't depend on the time, then you may try this

select login, status, date_modified from xdsl where login = 'zack_661' and trunc(date_modified)=to_date('05-MAR-14','DD-MON-YY');

Upvotes: 3

KevinKirkpatrick
KevinKirkpatrick

Reputation: 1456

Oracle DATE is stored to nearest second. When you query DATE_MODIFIED, Oracle will format the date using your NLS_date_format. Annoyingly, the default setting of this parameter does not display the hour/minute/second portion of the date value. So by default, it will look like your DATE values are just whole-dates, even though they are not. In your case, DATE_MODIFIED is almost certainly not exactly '05-MAR-2014' (which is actually 12:00 a.m. on march 5). Hopefully the following example makes this clearer:

SQL> CREATE TABLE EXAMPLE (DATE_MODIFIED DATE);

Table created.

SQL> INSERT INTO EXAMPLE VALUES (SYSDATE);

1 row created.

SQL> COLUMN DATE_MODIFIED FORMAT A20
SQL> SELECT * FROM EXAMPLE;

DATE_MODIFIED
--------------------
04-MAR-14

SQL> SELECT * FROM EXAMPLE WHERE DATE_MODIFIED = '04-MAR-14';

no rows selected

SQL> SELECT TO_CHAR(DATE_MODIFIED,'DD-MON-YYYY HH24:MI:SS') DATE_MODIFIED FROM EXAMPLE;

DATE_MODIFIED
--------------------
04-MAR-2014 21:40:45

SQL> SELECT * FROM EXAMPLE WHERE DATE_MODIFIED = TO_DATE('04-MAR-2014 21:40:45',
'DD-MON-YYYY HH24:MI:SS');

DATE_MODIFIED
--------------------
04-MAR-14

SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> SELECT * FROM EXAMPLE;

DATE_MODIFIED
--------------------
04-MAR-2014 21:40:45

SQL>

Upvotes: 2

Gundamaiah
Gundamaiah

Reputation: 786

May be this will help you 

select login, status, date_modified from xdsl where login = 'zack_661' and date_modified =to_date('05-MAR-14','DD-MON-YY');

Upvotes: 0

souvickcse
souvickcse

Reputation: 7804

You can use this 

  where date_modified  = to_date('31-DEC-95','DD-MON-YY')

Upvotes: 0

Related Questions