Sorting by a Date in a Text Field

2 posts by 2 authors in: Forums > CMS Builder
Last Post: December 24, 2012   (RSS)

By sidcam - December 23, 2012

On my list view page (and if possible my editor) I would like to set the default sorting of my MLS listing data to the record's entry day (newest first) and then price (highest first).  Because I do a full refresh of the data nightly, I unfortunately can't use the system's "createdDate" field (because they're all today's date)

Anyway, I do have two fields in the downloaded data for this:

"entry_date" and "listing_price"

My problem is that "entry_date" is imported as a text field and the data as it's imported looks like this.

Record #1: "2012-12-22T21:51:12.363"

Record #2: "2012-12-22T20:36:52.657"

etc

Because it's a text field, the editor's sort is looking at the whole field (not just the year, month and date) which makes every record unique (killing the ability to sub-sort the day's entry by price).

In searching the forum the best answer I could find was to change the field to a date field, but that generated an error message- apparently the data isn't formatted to CMSB's idea of a date.

So questions...

In the sort tab of the section editor, is it possible to limit "entry_date DESC" so that it only looks at the first 10 characters of the field? (i.e. "2012-12-22")

If I give up trying to sort this way in the editor (which is fine), how would I place this sort limit on the list view page (where I really need it)?

If it can't easily be done in CMSB, I am using a Perl script to clean up the data's txt file before it's imported (using simple substitution commands).  What would that field's data need to look like in order for CMSB to recognize it as a date?  It seems cleaning up the data to make it a CMSB date might be the easiest way to go.

Thanks in advance for your help.

Sid