Order by date

Bruno Camargo

<p>I want to order the results of the report by date.</p>
<p>The select goes like this:</p>
<p> </p>
<div>select *</div>
<div>from ML001012 f</div>
<div>where</div>
<div>f.VERSION = (select max(VERSION) from ML001012 where DOCUMENTID = f.DOCUMENTID) and</div>
<div>TO_DATE(TXT_PROD, 'dd/mm/yyyy') between </div>
<div>TO_DATE(?, 'dd/mm/yyyy') and</div>
<div>TO_DATE(?, 'dd/mm/yyyy')</div>
<div>order by TO_DATE(TXT_PROD, 'dd/mm/yyyy') asc</div>
<div> </div>
<div>But I'm getting results like :</div>
<div>01/10</div>
<div>01/11</div>
<div>01/12</div>
<div>02/08</div>
<div>02/09</div>
<div>02/12</div>
<div>Instead of:</div>
<div>02/08</div>
<div>02/09</div>
<div>01/10</div>
<div>01/11</div>
<div>01/12</div>
<div>02/12</div>
<div> </div>
<div>What is funny is that I have another report with the same query as described above, but in THAT report, the order by works.</div>
<div> </div>
<div>I attached the 2 reports so you guys can take a look(info_terceiros_estoques.rptdesign is the one where the order by works perfectly). </div>
<div>Thanks in advance.</div>

pricher

<p>Hi,</p>
<p> </p>
<p>This seems to me more like a SQL issue than a BIRT issue, so you should investigate first what results you get by issuing the SELECT statement directly to the database. Also, when I looked at your sample reports, you have a SQL Server connection, but your query includes a TO_DATE function that doesn't exist out-of-the-box in SQL Server. When I tested using the CONVERT function, my results were fine. The following query:</p>
<pre class="_prettyXprint _lang-sql">
select myDate
, convert(DATETIME, myDate, 103) as 'theDate'
from test
order by convert(DATETIME, myDate, 103)
</pre>
<p>gives me the following results:</p>
<p> </p>
<p>