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.
How about something like this for the ORDER BY:
DATE(entry_date) DESC, listing_price DESC
The MySQL DATE() function convert a DATETIME string to just a date and in my tests seemed to work with the date examples you provided.
Let me know if that works for you.