how to use = assignment operator with timestamp date column in oracle

Question

I'm using timestamp in dat column in table r3. when I fire command

select dat from r3 where dat='16-nov-09';

it shows "no rows selected" but when i fire command

select dat from r3 where dat>'15-nov-09';

it shows the whole data of 16-nov-09. Tell me what is wrong in my first command or what i have to do.

Answer 1

use the below format for a date field in where condition.

where to_char(DAT,'mmddyyyy') = '11152009';

Answer 2

As DAT is timestamp you can use as below

select DAT from R3 
where DAT between to_date('16-NOV-09' , 'dd-MON-yy') and to_date('16-NOV-09 23:59:59', 'DD-MON-YY hh24:mi:ss')

Answer 3

Timestamp has time and date components, so query

select dat from r3 where dat='16-nov-09';

will work only for records where time component is midnight: '00:00:00'

Beside formatting (to_date function), you can truncate timestamp to get only date:

select dat from r3 where trunc(dat)='16-nov-09';

Beware that this will not use index on field dat (if there is any).

Answer 4

In Oracle the date fields also contain a time component, so 16-nov-09 is actually midnight of Nov 16th.

Two different ways to handle this:

where to_char(DAT,'mmddyyyy') = '11152009'

as john suggested, but I like the following version more:

where trunc(dat) = to_date ('11152009', 'mmddyyyy')

TRUNCfor a date "removes" the time component (or to be more specific, truncates it to midnight), and to_date is the proper way to construct a date value in Oracle SQL. (I prefer to do the comparisons in the right domain - DATEs as in the second example- over another - STRINGs as in the first example. With strings you may run into some weird month issues, sorting is easier in dates etc.)

Answer 5

Quering on oracle date columns is always confusing. The date columntype is always a datetime. Storing the current date from sysdate stores always the time component too.

There good and evil ways quering the date columns. I show and vote some.

where to_char(DAT, 'DD-MON-YYYY') = '16-NOV-2009'

where trunc(DAT) = to_date('16-NOV-2009', 'DD-MON-YYYY')

Both bad, because they do not use any index. To avoid this, you can define a function based index on the expression. The trick of both is to cut off the time component. If time is not needed, than it is a good advise to cut off the time in INSERT and UPDATE trigger. The function based index can convert to a normal index.

where DAT between to_date('16-NOV-2009', 'DD-MON-YYYY') 
          and to_date('16-NOV-2009 23:59:59', 'DD-MON-YYYY HH24:MI:SS')

where DAT >= to_date('16-NOV-2009', 'DD-MON-YYYY') and DAT < to_date('16-NOV-2009', 'DD-MON-YYYY')+1

This two are always my favorites. Its a good advice to use to_date and to_char to convert the values between string and datetime.

Answer 6

TIMESTAMP and DATE are different data types in oracle and both store time components. If you really do need to store subsecond times then you use TIMESTAMP, otherwise DATE is your best choice.

The ANSI timestamp and date literal syntaxes are quite handy:

create table ts_test (ts1 timestamp);

select *
from   ts_test
where  ts1 > timestamp '2009-10-11 00:00:00'
/

select *
from   ts_test
where  ts1 > timestamp '2009-10-11 00:00:00.1'
/

select *
from   ts_test
where  ts1 > timestamp '2009-10-11 00:00:00.001'
/

select *
from   ts_test
where  ts1 = date '2009-10-11'
/