How to sort date in mssqlserver

Question

I want to get individual dates from my dbtable named tblFormno2 in ascending order. For this, I wrote the following query, but it does not work properly.

Date_submit column declared as datetime

select distinct (convert(nvarchar(100),date_submit,103)) as dob from tblFormno2 order by dob asc 

Here the output is displayed as

 05/07/2011 06/03/2011 06/07/2011 07/04/2011 08/01/2012 

instead

 06/03/2011 07/04/2011 05/07/2011 06/07/2011 08/01/2012 

How to solve this problem???

Answer 1

What about

 select convert(nvarchar(10), date_submit_inner, 103) as date_submit from ( select distinct date_submit as date_submit_inner from tblFormno2 ) as T order by T.date_submit_inner asc 

Answer 2

Your order by does not sort by date_submit from the table. Is sorts by the named date_submit output date_submit . If you give the table name in order, it should work. If this does not work, try giving it a different name than the table column.

 select distinct (Convert(nvarchar(100),date_submit,103)) as date_submit from tblFormno2 order by tblFormno2.date_submit asc 

Answer 3

 create table #temp ( DT varchar(20) ) Insert into #temp(DT)values('13/05/2011') Insert into #temp(DT)values('03/06/2011') Insert into #temp(DT)values('07/06/2011') Insert into #temp(DT)values('04/07/2011') Insert into #temp(DT)values('01/08/2011') Select * from #temp Below are the database records... 

---

 select (convert(varchar,Dt,107)) t into #t from #temp select * from #t 

 drop table #temp drop table #t