Order by date field

I have a report which includes a date field (format is dd/mm/yy), I want these dates to appear in ascending order but when I include "order by (date field)" in the sql the dates appear in order of the dd value. E.g. 04/04/12, 07/07/12, 12/03/12.

I`ve also specified the order in the sorting for the table but it doesn`t seem to help.

This has got to be easy, help a newbie out please.

Find more posts tagged with

Comments

bgbaird

Perhaps the easiest way is to sort in the "sort hints" of the Data Set, instead of in the SQL. I've said it before, and I'll say it again, "Dates will be the death of me".

Brian

Tinwolf

<blockquote class='ipsBlockquote' data-author="'bgbaird'" data-cid="113628" data-time="1359388136" data-date="28 January 2013 - 08:48 AM">
Perhaps the easiest way is to sort in the "sort hints" of the Data Set, instead of in the SQL. I've said it before, and I'll say it again, "Dates will be the death of me". 
 
Brian </blockquote>
 
Sorry Brian I`m not with you 
"sort hints"? 
 
Paul

linucksrox

This could be wrong, but my first thought would be to try using DATE_FORMAT(): 
<pre class='_prettyXprint _lang-auto _linenums:0'>ORDER BY DATE_FORMAT("04/04/2012", "%d/%m/%Y")</pre>
This is using MYSQL, all the reference information is <a class='bbc_url' href='http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format'>here</a>.

bgbaird

In the data set object, after you enter SQL, there will be a choice above "Preview Results" call "Sort Hints"

Tinwolf

Hi Brian

Maybe I`ve got an older version of BIRT (2.5.2) I don`t have Sort Hints

Hi linucksrox (if that is your real name)

I`ve tried using you syntax but I just get errors

bgbaird

I don't have an environment to go back to 2.5

What DB and what is your SQL?

linucksrox

Maybe you are using a different version of SQL. Check which version of SQL you're using, and then see if there's a date format function for it. My example is valid in MYSQL.

The other option which may be easier to implement would be to just sort using a table or list. When you create a new table and bind it to your dataset, highlight the table and click the binding tab in the property editor. Make sure your date field data type is set to "Date". Then you can click the sorting tab on the table and sort by that date. BIRT will automatically figure out how to do this.

I'm running BIRT 3.7.2 and don't see the sort hints either.

Tinwolf

The data is from good old MS sql server and the code I`ve written is:

order by
quotations.chasedate

bgbaird

Silly question? is "quotations.chasedate" a true DT field?

"sp_help quotations" will tell you.

Tinwolf

<blockquote class='ipsBlockquote' data-author="'bgbaird'" data-cid="113642" data-time="1359394923" data-date="28 January 2013 - 10:42 AM">
Silly question? is "quotations.chasedate" a true DT field? 
 
"sp_help quotations" will tell you. </blockquote>
 
The chase date is a date/time field