How to query the date whose value is in text format

Hello,

I get a problem in developing the company report. The column of "BATCH" in Oracle view is shared by all departments that need the different entries, so we have to use "Text" data type for this column in order meet all departments needs.

One department uses it as a sample date column. The text entry value is in the format of mm/dd/yyyy.

If I do not do the data converting, when I set the query: Where SampleDate between '01/01/2005' and '01/31/2005', the outcome not only include the January data of 2005 but also include the January data from all privious years, because system evaluates the data in text format rather than date format.

What I have done is to use Oracle To_Date(SampleDate,'mm/dd/yyyy') function to convert the text format to Oracle date format in report dataset.

I have two problems:

1) How to take out the wrong format enties? for example, user enters 1102/2005 instead of 11/02/2005. I find that the wrong formating entries cause the failure of data retrival. Micro T-SQL has a function of IsDate() to flag out the entries that are not in date formating, but it does not work here, I guess the reason may be our data source is Oracle.

2) Even the data is conveted, it still does not work propertly.

For example, I write:

Where To_Date(Batch,'mm/dd/yyyy') between '01/01/2005' and '01/31/2005'

it does not work

I write: Where To_Date(Batch,'mm/dd/yyyy') between To_date('01/01/2005','mm/dd/yyyy') and To_date('01/31/2005','mm/dd,yyyy')

it still does not work.

How to resolve these issues.

Thanks,

Zixing

For problem #1, if you are using Oracle 10g you can use a regular expression in your query so you only get valid matching dates. If you aren't using 10g, then you could use the like operator with some wildcards.

For problem #2, try running your query in an Oracle client first just to make sure that you have the query syntax right. I am not familiar with Oracle tools for performing queries but I am thinking something like MSSQL Query Analyzer.