How to search date in the string field in CMOD

[Karthikkumark]

Hi All,

In CMOD we have "created_date" field which is ascii data type. But we need to search date type value.

Eg: created_date field has the value as "12312012" when we are search like below we are getting the no records.

between '12312012' and '01302013' . I know that it is search like string.

Is there any option available in cmod like below sql operation.

to_date(created_date,'mmddyyyy') >= to_date('12012012','mmddyyyy')

Thanks,
Karthik k

[Justin Derrick]

Hi Karthik.

Is there a reason you're not converting this to a date field?  I can't imagine a scenario where you'd want to choose to store a date as a string -- precisely because it doesn't allow the types of searches that you're trying to perform.

-JD.

[ewirtz]

Hi Karthik,
performant searches are only possible if it is a date field or stored as yyyymmdd. Otherwise you cannot use efiiciently any index if you look for '>='  or a range of dates. This implies you will always need a tablespace or at least a full index scan.

regards

Egon

[Karthikkumark]

Hi JD,

Earlier we used to oracle DB to fecth the datarows . where we stored the data as string. We were using the below (sql) query to fetch the result.

to_date(created_date,'mmddyyyy') >= to_date('12012012','mmddyyyy') .

Now we are moving the document to CMOD . where we having the column as string and storing the same vale. But while reterving the result we need to search as date  in stead of String.

Is there any pre condition available in ODWEK like above. (to_date(created_date,'mmddyyyy') )

Thanks,
karthik k

[Justin Derrick]

Hi Karthik...

I'm pretty sure you'll need to convert that data from a string to a date field inside the database, because queries are performed at the database level, and converting from string-to-date at the web-app level isn't going solve anything.

-JD.