4

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

ksg
  • 3,927
  • 7
  • 51
  • 97

3 Answers3

1

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
Pankaj
  • 9,749
  • 32
  • 139
  • 283
cadrell0
  • 17,109
  • 5
  • 51
  • 69
  • Yes its not sorting by the date_submit from the table.I've modified the above query to avoid confusion.I tried this but i'm getting error as "ORDER BY items must appear in the select list if SELECT DISTINCT is specified." – ksg Feb 13 '12 at 17:38
1

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
Alex K.
  • 171,639
  • 30
  • 264
  • 288
  • Here the problem is duplication of date occurs.I think its because when we take distinct here same date is occuring twice in the case where time is different. – ksg Feb 13 '12 at 17:44
  • You will get dups is there are different *times* for the same date of so change to `select distinct cast(date_submit as date) as date_submit_inner from tblFormno2` – Alex K. Feb 13 '12 at 17:46
  • I'm getting an error as "Type date is not a defined system type.".I think there's no datatype such as date in mssqlserver2005 – ksg Feb 13 '12 at 17:52
  • In that case you want `select distinct dateadd(d, 0, datediff(d, 0, date_submit)) as date_submit_inner from tblFormno2` – Alex K. Feb 13 '12 at 17:54
  • Thank you alex for this wonderful answer.Im new to sql ,so can you help me in understanding this dateadd function – ksg Feb 13 '12 at 17:58
  • Its just a quick way to strip the time from a datetime; `datediff(d, 0, date_submit)` is an int with days since 01/01/1901, then use `datediff` to convert that int back to a datetime that conveniently has no time part (http://stackoverflow.com/questions/2639051/what-is-the-best-way-to-truncate-a-date-in-sql-server). – Alex K. Feb 13 '12 at 18:02
-1
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
Pankaj
  • 9,749
  • 32
  • 139
  • 283