OnDemand Users Group

Support Forums => ODWEK, Java and REST APIs => Topic started by: Karthikkumark on November 25, 2013, 06:06:17 AM

Title: How to search date in the string field in CMOD
Post by: Karthikkumark on November 25, 2013, 06:06:17 AM
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
Title: Re: How to search date in the string field in CMOD
Post by: Justin Derrick on November 25, 2013, 04:14:14 PM
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.
Title: Re: How to search date in the string field in CMOD
Post by: ewirtz on November 26, 2013, 07:13:33 AM
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
Title: Re: How to search date in the string field in CMOD
Post by: Karthikkumark on November 26, 2013, 07:22:56 AM
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
Title: Re: How to search date in the string field in CMOD
Post by: Justin Derrick on November 26, 2013, 02:47:04 PM
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.