Quantcast

Trying to use a date in the data filter

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Trying to use a date in the data filter

Chris Entwistle
 Message from: JCarrott

I have tried using a date field in the data filter. I want all the data for 12/18/2009 but I keep getting a format error

_______________________________________

Historical Messages


Author: John Carrott
Date: Wed Dec 30 08:29:09 PST 2009
I have tried using a date field in the data filter. I want all the data for 12/18/2009 but I keep getting a format error
__
_______________________________________

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

RE: Trying to use a date in the data filter

Ed Klinger
You are entering a string instead of a date.  Date are dates and strings are
strings and you just tried to compare a date to a string this is why you are
getting the formatting error.
 
Try entering to_date('12/18/2009','mm/dd/yyyy') into the filter for your date
field.  
 
If your date field has the time portion set then you have 2 options
 
BETWEEN TO_DATE( '12/18/2008 00:00:00', 'mm/dd/yyyy hh24:mi:ss' )
       AND TO_DATE( '12/18/2008 23:59:59', 'mm/dd/yyyy hh24:mi:ss' )
 
or
 
TRUNC(date_column) = to_date('12/18/2009','mm/dd/yyyy')
 
But be aware that using TRUNC on your column will cause the optimizer to not use
the index if you have one on that column.
 
Ed
[TeamT]

  _____  

From: [hidden email] [mailto:[hidden email]] On Behalf Of John
Carrott
Sent: Wednesday, December 30, 2009 11:29 AM
To: [hidden email]
Subject: [toad] Trying to use a date in the data filter





Message from: JCarrott



I have tried using a date field in the data filter. I want all the data for
12/18/2009 but I keep getting a format error

_______________________________________

Historical Messages


Author: John Carrott
Date: Wed Dec 30 08:29:09 PST 2009
I have tried using a date field in the data filter. I want all the data for
12/18/2009 but I keep getting a format error
__
_______________________________________







********************************************************************************************
Notice of Confidentiality: This e-mail communication and the attachments hereto,
if any, are intended solely for the information and use of the addressee(s) identified
above and may contain information which is legally privileged and/or otherwise confidential.
If a recipient of this e-mail communication is not an addressee (or an authorized
representative of an addressee), such recipient is hereby advised that any review,
disclosure, reproduction, re-transmission or other dissemination or use of this e-mail
communication (or any information contained herein) is strictly prohibited. If you are
not an addressee and have received this e-mail communication in error, please advise the
sender of that circumstance either by reply e-mail or by telephone at (407) 384-8818
immediately delete this e-mail communication from any computer and destroy all physical
copies of same.

Replies Filtered: Any incoming e-mail reply to this communication will be electronically
filtered for "spam" and/or "viruses."   That filtering process may result in such reply
being quarantined (i.e., potentially not received at our site at all) and/or delayed in
reaching us. For that reason, we cannot guarantee that we will receive your reply and/or
that we will receive it in a timely manner. Accordingly, you should consider sending
communications to us which are particularly important or time-sensitive by means other
than e-mail.
********************************************************************************************
Loading...