如何在mssqlserver中对日期进行排序

时间:2022-03-01 14:03:46

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.

我希望以升序的方式从我的dbtable命名为tblFormno2获取不同的日期。为此,我编写了以下查询,但它无法正常工作。

Column date_submit is declared as datetime

列date_submit声明为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 ???

如何解决这个问题呢 ???

3 个解决方案

#1


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

#2


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.

您的订单不是按表中的date_submit排序。是按date_submit的命名输出列进行排序。如果您按顺序特定表名,则应该可以正常工作。如果这不起作用,那么尝试为输出提供与表列不同的名称。

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

#3


0  

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

如何在mssqlserver中对日期进行排序

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

select * from #t

如何在mssqlserver中对日期进行排序

drop table #temp
drop table #t

#1


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

#2


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.

您的订单不是按表中的date_submit排序。是按date_submit的命名输出列进行排序。如果您按顺序特定表名,则应该可以正常工作。如果这不起作用,那么尝试为输出提供与表列不同的名称。

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

#3


0  

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

如何在mssqlserver中对日期进行排序

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

select * from #t

如何在mssqlserver中对日期进行排序

drop table #temp
drop table #t