Sorting by a Date in a Text Field

2 posts by 2 authors in: Forums > CMS Builder
Last Post: January 14, 2013   (RSS)

By sidcam - January 14, 2013

Asked this one over the holidays, thought I'd give it another shot.

I have tables of MLS real estate listing data that are fully refreshed by a nightly import.  I would like to set the default sorting to the record's entry date (newest first) and then price (highest first) on my list view page (and if possible my editor).  Unfortunately I can't use CMSB's "createdDate" field because every record is today's import date!

The downloaded data does have fields for this ("entry_date" and "listing_price") but "entry_date" is imported as a text field and the data also includes the entry time.  As a result, when I setup the sort in the editor, every record ends up having a unique date field which ruins the second sort on price.

Example of the data:

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

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

etc.

So my questions are...

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. just the date portion of the data or "2012-12-22")

If I can't sort this way in the editor (which is fine) how would I place this limit on just the list view page?

In searching the forum the best answer I could find was to change the field to a date field, but when I tried that it generated an error message.  I'm assuming it's because the imported data isn't formatted to CMSB's idea of a date.  If this is the only solution that will work, what would that field's data need to look like prior to importing?

Thanks in advance for your help.

Sid