We help IT Professionals succeed at work.

Equating the date column field

RaoVP
RaoVP asked
on
Hello,
I have the collection_date column in my oracle DB the datatype is date.The format of the date is 09-09-2010 11:00:00 AM.
When i am wrting the sql query i have equate this collection date field to some hard coded value like 09-09-2010?
is there any conversion for this?
Comment
Watch Question

Most Valuable Expert 2011
Top Expert 2012
Commented:
These two statements are in conflict...
"the datatype is date  "

" The format of the date is 09-09-2010 11:00:00 AM."

dates don't have formats  strings do.  dates are dates.

if you want to compare a date to a hard coded string of 09-09-2010.  try this...

where collection_date >= to_date('09-09-2010','mm-dd-yyyy')
and collection_date < to_date('09-09-2010','mm-dd-yyyy') + 1

I had to guess at the mm-dd vs dd-mm.  Since 09-09 is the same either way.
if you use day before month in your string, the change mm-dd to dd-mm

Most Valuable Expert 2011
Top Expert 2012

Commented:
the reason for the >= and <  instead of a single equality is because dates always have a time component.

If you want all date values for  the day 09/09/2010 then you must do a range query.

when 09/09/2010  is converted to a date it will have time 00:00:00 (even if you don't display it)
Most Valuable Expert 2011
Top Expert 2012

Commented:
you "could" also do something like this...


where trunc(collection_date) = to_date('09-09-2010','mm-dd-yyyy')

but doing that causes any indexes on collect_date to be ignored and hence is poor practice

Author

Commented:
@SDSTUBER
is there any way i can equate it to particular month for eg like collection_date=09-2009
awking00Information Technology Specialist

Commented:
sdstuber is a stickler (and rightly so) for not using functions on attributes having indexes since the indexes will not be used and result in diminished performance. However, if you are going to be making lots of queries of this type, you could also create a function-based index on trunc(collection_date), then use the trunc(collection_date = to_date('20100909','yyyymmdd') method.
Most Valuable Expert 2011
Top Expert 2012

Commented:
where collection_date >= to_date('09-2010','mm-yyyy')
and collection_date < add_months(to_date('09-2010','mm-yyyy'), 1)
awking00Information Technology Specialist

Commented:
Didn't see that last question before my post, but the answer is there are also a number of ways of doing that. Obviously, you're not looking for a true equality condition, but one in which the collection date is in the same month and year as the compared to date (which still has a day and time component). I'll let sdstuber respond to this latest condition, since you addressed him directly.
awking00Information Technology Specialist

Commented:
And that's precisely the one I would have selected, Sean :-)
Most Valuable Expert 2011
Top Expert 2012

Commented:
I did consider mentioning the function based indexes as suggested by awking00, but if there is already an index on the column adding the function based on seems superfluous ( to me ) since the functionality can be achieved without the extra object

Plus, the trunc solution, wouldn't solve the followup question of a month-based filter, unless you also created a function based index on trunc(collection_date,'mm')

which then begs the questions...  what about filters for an hour? or a year?  or quarter? or a week? etc.

I find the range-based method of queries, is good practice because it doesn't need extra objects, and is versatile for all the other ranges as well.

and, if you have multi-timezone data.  TRUNC might not work either.

My Eastern Timezone Day  is not the same as somebody else's  Pacific Timezone Day.

So, even if have a trunc() function based index, it would only be usable for one set of users, not both.


Most Valuable Expert 2011
Top Expert 2012

Commented:
awking00,  I figured you'd do the same too,  but it was a good idea to mention the FBI's.  Even if they might not be appropriate here,  they can be very useful when applicable.

So, while I do discourage their use here and in general for these types of questions, I don't want to poo-poo your contribution.