How to search date in the string field in CMOD

Previous topic - Next topic

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.
Call:  +1-866-533-7742  or  eMail:  jd@justinderrick.com
IBM CMOD Wiki:  https://CMOD.wiki/
FREE IBM CMOD Webinars:  https://CMOD.Training/
IBM CMOD Professional Services: https://CMOD.cloud

Interests: #AIX #Linux #Multiplatforms #DB2 #TSM #SP #Performance #Security #Audits #Customizing #Availability #HA #DR

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.
Call:  +1-866-533-7742  or  eMail:  jd@justinderrick.com
IBM CMOD Wiki:  https://CMOD.wiki/
FREE IBM CMOD Webinars:  https://CMOD.Training/
IBM CMOD Professional Services: https://CMOD.cloud

Interests: #AIX #Linux #Multiplatforms #DB2 #TSM #SP #Performance #Security #Audits #Customizing #Availability #HA #DR