1. 

   shem, Member

   mysql / mydbr question:
   I am retrieving a date field from a mysql table and displaying it in a mydbr report using
   DATE_FORMAT(`loan`.`sale_date`, '%m/%d/%Y') AS 'Sale Date',

   I am discussing the sorting that should take place when you click on the "Sale Date" column header. It is supposed to sort the column.

   It usually works but sometimes it doesn't.
   And it seems that on one server it always works but on a different server there seems to be a problem.

   Any ideas?
2. 

   myDBR Team, Key Master

   myDBR displays and sorts columns based on their datatype. Dates are shown according to user or system preferences. When you use the DATE_FORMAT function, it converts the date to a string, and then myDBR sorts the column as a string.

   You have couple of options:
   

   1. Set the system date format to your desired format (allowing users to change it as needed) and avoid using the DATE_FORMAT function to keep the date as a date.
   2. Use the dbr.sortcol command to define a real date column as a sort column for the column that uses the DATE_FORMAT function.
   
   

   It is recommended to keep dates as dates. If the sale_date column is a datetime and you wish to display it as a date, use the CAST function.

   --
   myDBR Team

Reply

You must log in to post.