CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sqlsql-server-2008
Akshay J
Akshay J

Reputation: 5468

Select From View | Order By Not Working

I have one view named [AccountsLedger] with a date field name [Date]. When I use this query, the order by does not work:

SELECT CONVERT(varchar,[Date],103) as [Date]
                          ,[VoucherType]
                          ,[BillNo]
                          ,[Debit]
                          ,[Credit]
                      FROM [AccountsLedger]
                      order by [Date]

Results:

Date    VoucherType BillNo  Debit   Credit
01/06/2011  SALE    18  8400.00 0.00
03/06/2011  BEEJAK  15  0.00    24944.40
12/12/2009  PAYMENT 1   1000.00 0.00
12/12/2011  JOURNAL 1   800.00  0.00
12/12/2012  RECEIPT 4   0.00    1200.00
12/12/2016  RECEIPT 5   0.00    600.00

Please help.

Upvotes: 1

Views: 959

Answers (2)

Alex Aza
Alex Aza

Reputation: 78477

Try this

SELECT CONVERT(varchar,[Date],103) as [Date]
                      ,[VoucherType]
                      ,[BillNo]
                      ,[Debit]
                      ,[Credit]
FROM [AccountsLedger]
ORDER by [AccountsLedger].[Date]

The problem is that your query is sorting by newly formed string value, that you named the same way as you datetime field is named in the table. So, in order to fix the issue you need to have different names for the result string value and the underlying field or specify table prefix.

Upvotes: 6

Tommi
Tommi

Reputation: 8608

Looks like the your field is of varchar type, not date. Try like this instead:

SELECT CONVERT(varchar,[Date],103) as [Date]
                          ,[VoucherType]
                          ,[BillNo]
                          ,[Debit]
                          ,[Credit]
                      FROM [AccountsLedger]
                      order by CONVERT(varchar,[Date],102)

Upvotes: 2

Related Questions