如何在storeprocedure中对日期列进行排序 [英] how to sort date column in storeprocedure
问题描述
hii ,,
这是我的专栏之一
转换(varchar(10),Paln_Date,103)+''+ Plan_Hour PlanTime
PlanTime
20/07/2013 8:00 AM
20/08/2013 8:00 AM
22/08/2013 8:00
23/07/2013 8:00 AM
26/07/2013 8:00 AM >
27/07/2013 8:00 AM
27/08/2013 8:00 AM
29/08/2013 8:00 AM />
30/07/2013 8:00
这就是当我说通过plantime订购时我得到的结果
但我希望按月顺序排序,明确日期
预计结果是这样的
$ /
06/12/2008
03/01/2009
01/02/2009
07/02/2009
plese建议
使用 CAST AND CONVERT [ ^ ]函数;)
SELECT CONVERT ( VARCHAR ( 10 ),DateTimeField, 121 ) AS DateOnly
FROM TableName
SELECT CONVERT ( VARCHAR ( 10 ),GETDATE(), 121 ) AS DateOnly
如果你不想显示时间然后使用
转换(varchar,Paln_Date,103)PlanTime
或仅用于排序as
按转换顺序(varchar,P lanTime,103)
选择 * 来自 TableName
order by convert ( varchar ,PlanTime, 103 )
有关日期格式的详细信息,请访问
SQL Server功能有助于将日期和时间值转换为字符串文字和其他日期和时间格式。 [ ^ ]
创建 proc GetApplications
@ SortField nvarchar ( 20 )
as
选择 * 来自应用
订单 按
case @ SortField
' FirstName' 然后 cast(FirstName as sql_variant )
' LastName' 然后 cast(LastName as sql_variant )
何时' ApplyDate' 然后 cast(ApplyDate as sql_variant )
else cast( ID as sql_variant )
end
hii ,,
this is one of my column
Convert(varchar(10),Paln_Date,103) + ' ' + Plan_Hour PlanTime
PlanTime
20/07/2013 8:00 AM
20/08/2013 8:00 AM
22/08/2013 8:00 AM
23/07/2013 8:00 AM
26/07/2013 8:00 AM
27/07/2013 8:00 AM
27/08/2013 8:00 AM
29/08/2013 8:00 AM
30/07/2013 8:00 AM
this is how i am getting result when i say order by plantime
but i want sort properly month wise ,year n date wise
expected result is something like this
06/12/2008
03/01/2009
01/02/2009
07/02/2009
plese suggest
Use CAST and CONVERT[^] functions ;)
SELECT CONVERT(VARCHAR(10), DateTimeField, 121) AS DateOnly FROM TableName
SELECT CONVERT(VARCHAR(10), GETDATE(), 121) AS DateOnly
Hi,
if you dont want to show time then use
Convert(varchar,Paln_Date,103) PlanTime
or use it only for sorting as
order by convert(varchar,PlanTime,103)
select * from TableName order by convert(varchar,PlanTime,103)
for more details on date format visit
SQL Server Functions that helps to convert date and time values to and from string literals and other date and time formats.[^]
create proc GetApplications @SortField nvarchar(20) as select * from Applications order by case @SortField when 'FirstName' then cast (FirstName as sql_variant) when 'LastName' then cast (LastName as sql_variant) when 'ApplyDate' then cast (ApplyDate as sql_variant) else cast (ID as sql_variant) end
这篇关于如何在storeprocedure中对日期列进行排序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!