Sorting Date Column problem

Talk about Sorting & Paging problems, issues etc.

Moderators: ne_moj, zewa, vadimako, ne_moj, zewa, vadimako

kasumovic
Expert
Expert
Posts: 87
Joined: Sep 7th, '10, 22:38

Sorting Date Column problem

Postby kasumovic » Oct 24th, '10, 22:06

Hi,

I have following problem, when i modify for display date in "dd.mm.yyy" format, if i click on DATE colum it sort's it like "STRING" and not like field Value !

here is my sql :
==============
SELECT id,first_name,last_name,phone,date_format(birth_date,'%d.%m.%Y') as birth_date FROM customer

and here is debug SQL :
======================
SELECT id,first_name,last_name,phone,date_format(birth_date,'%d.%m.%Y') as birth_date FROM customer order by 4 DESC LIMIT 0,25

It's normal that is wrong Sorting order cause the query is sorted on field index and not on fieldname

What is right way , or is there a workarround ?

administrator
Site Admin
Site Admin
Posts: 5959
Joined: Jan 7th, '09, 23:18
Contact:

Re: Sorting Date Column problem

Postby administrator » Oct 25th, '10, 08:11

Leave in SELECT non-formatted birth_date and make sorting on this field by:

1. SELECT id,first_name,last_name,phone,birth_date,date_format(birth_date,'%d.%m.%Y') as my_birth_date FROM customer

2. "my_birth_date" = > array(............"sort_by"=>"birth_date" )

kasumovic
Expert
Expert
Posts: 87
Joined: Sep 7th, '10, 22:38

Re: Sorting Date Column problem

Postby kasumovic » Oct 25th, '10, 18:09

Ok this works,

I have try to solve this with :
"my_birt_date" =>array("header"=>"Birthdate", "header_aling"=>"center","type"=>"datedmy('dd.mm.yyyy')","width"=>"70px", "align"=>"center", "wrap"=>"nowrap", "text_length"=>"-1", "case"=>"normal")

But this is not working, have read somewhere that with the Update is should work in this way too.

administrator
Site Admin
Site Admin
Posts: 5959
Joined: Jan 7th, '09, 23:18
Contact:

Re: Sorting Date Column problem

Postby administrator » Oct 26th, '10, 08:56

You have to write: type"=>"datedmy", not type"=>"datedmy('dd.mm.yyyy')

kasumovic
Expert
Expert
Posts: 87
Joined: Sep 7th, '10, 22:38

Re: Sorting Date Column problem

Postby kasumovic » Oct 26th, '10, 09:49

Viewmode :
==========
"bestell_datum" =>array("header"=>"Auftrag","header_align"=>"center", "type"=>"datedmy","width"=>"70px", "align"=>"center", "wrap"=>"nowrap", "text_length"=>"-1", "case"=>"normal","sort_by"=>"bestell_datum")
editmode:
=========
"bestell_datum" =>array("header"=>"Auftrag", "type"=>"datedmy", "width"=>"80px", "req_type"=>"rt", "title"=>"Auftrags-Datum","maxlength"=>"10","calendar_type"=>"floating" )


In both cases is not working i get "yyyy-mm-dd" :?

administrator
Site Admin
Site Admin
Posts: 5959
Joined: Jan 7th, '09, 23:18
Contact:

Re: Sorting Date Column problem

Postby administrator » Oct 27th, '10, 11:39

There is now such field "datedmy" in view mode - only in Add/Edit/Detail modes.

Please read this post:
viewtopic.php?f=33&t=1935


Return to “Settings: Sorting & Paging”