CODEWITHSUNDEEP
sql-server
ksg
ksg

Reputation: 4067

How to sort date in mssqlserver

I want to get distinct dates from my dbtable named tblFormno2 in an ascending order.For that i've written the following query but its not working properly.

Column date_submit is declared as datetime

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

Here the the output is shown as

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

instead of 

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

How to solve this problem ???

Upvotes: 4

Views: 1524

Answers (3)

cadrell0
cadrell0

Reputation: 17307

Your order by is not sorting by date_submit from the table. Is is sorting by the named output column of date_submit. If you specific the table name in the order by it should work. If that doesn't work, then try giving the output 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

Upvotes: 1

Pankaj
Pankaj

Reputation: 10095

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...

enter image description here

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

select * from #t

enter image description here

drop table #temp
drop table #t

Upvotes: -1

Alex K.
Alex K.

Reputation: 175748

How 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

Upvotes: 1

Related Questions