CODEWITHSUNDEEP
oracle-database
user1502952
user1502952

Reputation: 1420

Oracle date comparison as string and as date

1) Why is that this doesn't works

select * from table where trunc(field1)=to_date('25-AUG-15','DD-MON-YY');

select * from table where trunc(field1)=to_date('25/Aug/15','DD/MON/YY');

row is returned in above cases.

So, does this mean that no matter what format the date is there in field1, if it is the valid date and matches with 25th August, it will be returned ( it won't care what format specifier we specify at the right side of the query i.e. DD-MON-YY or DD/MON/YY or anything else) ?

2) but comparsion as string exactly works:

select * from table where to_char(field1)=to_char(to_date     

('25/AUG/15','DD/MON/YY'), 'DD/MON/YY');

no row is returned as the comparison is performed exactly.

I have field1 as '25-AUG-15' ( although it can be viewed differently doing alter session NLS_DATE_FORMAT...)

field1 is of DATE type

Any help in understanding this is appreciated specifically with respect to point 1

Upvotes: 0

Views: 93

Answers (2)

Andrei Amarfii
Andrei Amarfii

Reputation: 725

So you have a field of type DATE with value of The 25th of August 2015, but it could be visualized in different ways, what in fact is named format.

The DATE has format! The DATE has implicit format defined by Oracle, in your case it is DD-MON-YY, because you see your field as 25-AUG-15.

You can select your data without TO_DATE conversion, just matching this default format like this:

select * from table where trunc(field1)='25-AUG-15';

In fact, it's not recommended, because if someone will change the default format, Oracle will not be able to understand that you are going to tell him a DATE.

So the to_date conversion in this case:

select * from table where trunc(field1)=to_date('25/AUG/15','DD/MON/YY');

is used to specify that you wanna tell to Oracle a DATE type with value of 25th of August 2015, using a diffrent format, specified as second parameter. (DD/MM/YY in this case).

Upvotes: 2

mustaccio
mustaccio

Reputation: 19011

The DATE data type does not have format -- it's simply a number. So, a DATE 25-Aug-2015 is the same as DATE 25/AUG/15, as well as DATE 2015-08-15, because it's the same DATE.

Strings, on the other hand, are collections of characters, so '25-Aug-2015' is obviously different from '25/AUG/15'.

In the first example you are comparing DATE values. In the second example you are comparing strings.

Upvotes: 3

Related Questions