Then, you need to convert this string to a date using the STR_TO_DATE(date_string, '%Y %M %d') function. Since you'd like to get a string in the 'Year Month Day' format, the arguments are exam_year, exam_month, exam_day, and the spaces between them. You don't need to cast numbers to strings. The CONCAT() function combines all the arguments into one string. But first, you need to create a string using the CONCAT() function:ĬONCAT(exam_year, ' ', exam_month, ' ', exam_day) If you have a date stored as a string in the 'Year Month Day' format, you can cast it to a date using STR_TO_DATE(date_string, '%Y %M %d'). To do this, use the STR_TO_DATE() function. To sort by date, create date values from the year, the month, and the day values. The result looks like this (the rows are sorted in ascending order by exam_year, exam_month, and exam_date): subject ORDER BY STR_TO_DATE(CONCAT(exam_year, ' ', exam_month, ' ', exam_day), '%Y %M %d') The months are given in names, not in numbers. The exam table has the following columns: subject, exam_year, exam_month, and exam_day. Also, the rows with the same exam_date are displayed in random order (you may see Science third and Health fourth, or Health third and Science fourth). Note that in MySQL, NULLs are displayed first when sorting in ascending order and last when sorting in descending order. If you'd like to see the latest date first and the earliest date last, you need to sort in descending order. ![]() You could also use the ASC keyword to make it clear that the order is ascending (the earliest date is shown first, the latest date is shown last, etc.). This way, you'll sort the data in ascending order by this column. Use the ORDER BY keyword and the name of the column by which you want to sort. The result looks like this (the rows are sorted in ascending order by exam_date): subject The exam table has two columns, subject and exam_date. (I'm sorry for coming back to this after several weeks have elapsed, but I was diverted onto some other projects in the interim, and have just got back to this.You want to sort the rows by date in MySQL. Have I applied your suggestion incorrectly, or have I missed something else the is essential? (There are no Javascript error reported.) The sort order is not as defined above, but is alphabetical, as it was before I inserted the code above. I have included the plug-in in the HTML: ![]() I have the following code to define the enum for the sort-order: $.extend( $.fn.dataTable.enum( Where column 4 is the column I want to sort via the enum. I have the following in my DataTable: columnDefs: [ I've tried to implement the enum solution as you suggested, but I've got something wrong that I can't figure out. That is the type of sort I wanted to achieve when I asked my question. I thought I might include this example from another application (that does not use DataTables) here to illustrate what I had in mind originally: order by field(status, Then I can just sort that column (provided I can sort on a hidden column) because I would not want to display that.Īlso, related to Kevin's suggestion, the example of the order by field that I had in mind is simpler that what Kevin emulated. Now, looking at what Kevin's plugin does, I realized that I can make this issue go away by looking up the sort order for the content of this field (it has only a half-dozen possible values, from a select element) and storing that as another field in the database when I store the record. Or, do I just need to replace custom with enum in this case? ![]() I had seen the use of custom in the documentation, but I did not find anything that defined the type custom, so I thought I needed to find a way to define it.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |