CODEWITHSUNDEEP
sqlsql-serverstored-procedures
santhosha
santhosha

Reputation: 377

Report order by date column descending in sql server?

This is my query:

SELECT
  DR.name as tradername,
  convert(varchar,DR.Receiveddate,103) as recdate,
  DR.Recamount,
  DR.Chequeno,
  DR.Remark,
  DR.Updatedby 
FROM K_HM_ChicksaleDueReport DR 
where 
  DR.Receiveddate between @fromdate and @todate 
  and DR.name=@name

union all

Select 
  CS.name as tradername,
  convert(varchar,CS.chicksplaceddate,103) as recdate,
  CS.Recamount,
  '' as chequeno,
  'Direct' as Remark,
  CS.Updatedby 
from K_HM_ChickSales CS
where 
  CS.Recamount>0 
  and  CS.chicksplaceddate between @fromdate and @todate 
  and CS.name=@name 

order by recdate desc

Using this I am getting report by only day wise descending order. But what I want is total dd/mm/yyyy wise descending order.

Something like:

22/05/2014
21/05/2014
10/04/2014
 5/03/2014

Upvotes: 1

Views: 86

Answers (3)

Sambasiva
Sambasiva

Reputation: 1044

Try this 

select tradername,convert(varchar,recdate,103)as recdate,Recamount,chequeno,
remark,updatedby from (
SELECT  name as tradername,Receiveddate as recdate,Recamount,Chequeno,
Remark,Updatedby FROM K_HM_ChicksaleDueReport  
where Receiveddate between @fromdate and @todate 
and name like '%'+@name+'%'
union all
Select name as tradername,chicksplaceddate as recdate,Recamount,''as chequeno,'Direct' as Remark,Updatedby from K_HM_ChickSales 
where Recamount>0 and  chicksplaceddate between @fromdate and @todate and name like '%'+@name+'%')a   order by a.recdate  desc

end

else
select tradername,convert(varchar,recdate,103)as recdate,Recamount,chequeno,remark,updatedby from(
SELECT name as tradername,Receiveddate as recdate,Recamount, Chequeno,
Remark,Updatedby 
FROM K_HM_ChicksaleDueReport  where Receiveddate between @fromdate 
and @todate and name=@name
union all
Select name as tradername,chicksplaceddate as recdate,Recamount,''as chequeno,
'Direct' as Remark,Updatedby from K_HM_ChickSales 
where Recamount>0 and  chicksplaceddate between @fromdate and @todate 
and name=@name)a order by a.recdate  desc

Upvotes: 1

NickyvV
NickyvV

Reputation: 1746

Because recdate is a VARCHAR it orders by individual characters.

Try casting / converting recdate back to DATE first, so like this:

ORDER BY CAST(recdate AS DATETIME) DESC

Upvotes: 2

cankaya07
cankaya07

Reputation: 63

You have to change your query like that i guess

select tradername,
 convert(varchar,recdate,103) as recdate,Recamount,Chequeno,Remark,Updatedby from (
SELECT
  DR.name as tradername,
  DR.Receiveddate as recdate,
  DR.Recamount,
  DR.Chequeno,
  DR.Remark,
  DR.Updatedby 
FROM K_HM_ChicksaleDueReport DR 
where 
  DR.Receiveddate between @fromdate and @todate 
  and DR.name=@name

union all

Select 
  CS.name as tradername,
  CS.chicksplaceddate as recdate,
  CS.Recamount,
  '' as chequeno,
  'Direct' as Remark,
  CS.Updatedby 
from K_HM_ChickSales CS
where 
  CS.Recamount>0 
  and  CS.chicksplaceddate between @fromdate and @todate 
  and CS.name=@name 

) as o
order by recdate desc

Upvotes: 2

Related Questions