Sorting by a Date in a Text Field
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"
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.