Date field in extra fields - How?

[eschulze]

I am running the Developer 3.1.5b version and have a question about extra product fields. I have two fields which I want to be dates. How do I go about ensuring that the fields are entered as a date? I can format the field for display as a date, but it only works if the user enters a date format in the text field.

As a follow-up, I am displaying this fields on showlisting.tpl.php. I would like them to be sorted by clicking on the header ... right now it only sorts by alphabetical order. How do I get it to sort by the date?

[Blair]

All extra fields have a datatype of 'text' in the database. In order for your site to correctly handle the dates, I believe you would have to change the datatype to 'date' in the database. The problem with doing this is that all your other extra fields would throw errors...

I'm afraid there's no way to get this done.

To get the user to enter a date format, you could rename your extra field to something like "Date (MM/DD/YYYY)".

[eschulze]

Is there a way to assign a new field type and define it somewhere? If someone points me the right direction, I can give it a try.

[eschulze]

Is there a way to add the fields directly to the database, assign it as a date field and add it to the admin and user end? If someone could tell me which pages I would need to modify I would be willing to try this as well.

[Blair]

I'm afraid I may have inadvertently served up a red herring... sorry!

I talked to Eric about this and he said that, since the table is regenerated with Javascript, that it really shouldn't matter how it's stored in the database.

You gotta link where we can take a look at this?

Thanks-

[Blair]

Eric-

I found your site - don't worry about providing a link.

We think that you could leave everything like it is, except for one thing - use numbers instead of spelling out the month. This should let your users re-sort the results as expected...

Could you change a few and try it out, please?

Thanks-

[eschulze]

The dates are entered in the database as numbers. I am just re-formatting the field in showlistings the same way that the expiration and dateadded fields are formatted. I removed the formatting and the column is sorted wrong still. I'm sure it is because it is not being recognized as a date. I have removed the formatting, please see what I'm talking about.

http://www.prepvolleyball.com/camps/...?type=11&sec=9

[Blair]

OK - here's what's happening...

The javascript that resorts the table was developed by a European developer who has made the code freely available for use. As a result, the code assumes a date format of "DD/MM/YYYY" or "DD/MM/YY". If you take a look at your page, it's working as it's written because it's re-sorting by the second group of numbers, in this case, your "DD" value.

So, how about opening your javascript/sortable.js file and find the following:

function ts_sort_date(a,b) {
    // y2k notes: two digit years less than 50 are treated as 20XX, greater than 50 are treated as 19XX
    aa = ts_getInnerText(a.cells[SORT_COLUMN_INDEX]);
    bb = ts_getInnerText(b.cells[SORT_COLUMN_INDEX]);
    if (aa.length == 10) {
        dt1 = aa.substr(6,4)+aa.substr(3,2)+aa.substr(0,2);
    } else {
        yr = aa.substr(6,2);
        if (parseInt(yr) < 50) { yr = '20'+yr; } else { yr = '19'+yr; }
        dt1 = yr+aa.substr(3,2)+aa.substr(0,2);
    }
    if (bb.length == 10) {
        dt2 = bb.substr(6,4)+bb.substr(3,2)+bb.substr(0,2);
    } else {
        yr = bb.substr(6,2);
        if (parseInt(yr) < 50) { yr = '20'+yr; } else { yr = '19'+yr; }
        dt2 = yr+bb.substr(3,2)+bb.substr(0,2);
    }
    if (dt1==dt2) return 0;
    if (dt1<dt2) return -1;
    return 1;
}

I haven't tested it but I think it should be changed to:

function ts_sort_date(a,b) {
    // y2k notes: two digit years less than 50 are treated as 20XX, greater than 50 are treated as 19XX
    aa = ts_getInnerText(a.cells[SORT_COLUMN_INDEX]);
    bb = ts_getInnerText(b.cells[SORT_COLUMN_INDEX]);
    if (aa.length == 10) {
        dt1 = aa.substr(6,4)+aa.substr(0,2)+aa.substr(3,2);
    } else {
        yr = aa.substr(6,2);
        if (parseInt(yr) < 50) { yr = '20'+yr; } else { yr = '19'+yr; }
        dt1 = yr+aa.substr(0,2)+aa.substr(3,2);
    }
    if (bb.length == 10) {
        dt2 = bb.substr(6,4)+bb.substr(0,2)+bb.substr(3,2);
    } else {
        yr = bb.substr(6,2);
        if (parseInt(yr) < 50) { yr = '20'+yr; } else { yr = '19'+yr; }
        dt2 = yr+bb.substr(0,2)+bb.substr(3,2);
    }
    if (dt1==dt2) return 0;
    if (dt1<dt2) return -1;
    return 1;
}

Could you try that and let me know? If it doesn't work, take a look at what I've changed and play around with changing the substrings...

Thanks-

[eschulze]

That did it ... thanks!

[Blair]

Eric-

I'm glad to hear it!

One thing I forgot to include in my last post it this...

Keep in mind that the only results that are re-sorted are the results in the current table. So, anything on "page 2" is not included....