如何按日期字段按升序排序 [英] how to sort by Date field in ascending order
问题描述
您好，
在设计时我们现在已经将[DOJInGovrService]字段作为varchar数据类型给出了当我们按此参数（DOJInGovrService）按升序尝试排序时它没有给出所需的结果。我知道这是数据类型问题，但我现在无法更改dataype，因为数据已经输入。
选择 ED.class，ED.CurrentOfficePlace，ED.DOB，ED.DOJInCurrentOff，ED.DOJInGovrService，ED.DOJInSamvarg，ED.EmpName，ED.HomePlace，ED.Qualification from tbl_EmplyeesBiodata ED order by DOJInGovrService asc
输入日期的格式为28-08-2004
请帮帮我
将nvarchar值转换为datetime并对其进行排序......
订单 按 CONVERT （ datetime ，DOJInGovrService， 102 ） asc
102是日期时间格式的代码 - 请参阅此处的选项： https：/ /msdn.microsoft.com/en-us/library/ms187928.aspx [ ^ ]
如果您的DOJInGovrService是DATE或DATETIME列，那就是会工作。
但是......你的评论输入的日期格式为28-08-2004意味着它存储为一个字符串。如果是这样，最好的解决方案是首先修复数据库，并用最合适的数据类型替换所有存储为字符串的列：DATE，DATETIME，INT等等 - 继续你的方式只会导致SQL查询中的大量复杂化。
字符串按字符排序，因此第一个不同的字符决定顺序。你不需要那个，因为这对日期没用。不幸的是，虽然可以将字符串日期转换为查询中的DATE（但不一定可靠，取决于编码器和/或用户插入或更新了DB的数量），这是一个问题，从现在开始会让你麻烦你离开公司。
现在改变你的数据库，而代码库很小，而且数据也很小！如果你等待，问题将成为一个重大变化。现在有一个很小的改变，你现在的问题也会消失，以及未来的很多问题。
这是我的例子，希望这对你有所帮助。
我的表是：
Id（ int ）SortFields（ varchar （ 50 ））
1 28-08- 2004
2 29-08-2004
3 01-01-2001
4 15-11-2000
SQL查询：
< pre lang =SQL> SELECT *
FROM
（ SELECT [简单] .Id， CONVERT （ datetime ， [简单] .SortFields， 105 ） AS FDate
FROM [dbo]。[简单]） AS tb
ORDER BY tb.FDate ASC
结果：
Id FDate
4 2000-11-15 00:00:00。 000
3 2001-01-01 00:00:00。 000
1 2004-08-28 00:00:00。 000
2 2004-08-29 00:00:00。 000
Hello,
In design time we have given [DOJInGovrService] field as varchar datatype now when we are trying order by this parameter(DOJInGovrService) in ascending order it is not giving the desired result. I know it is datatype problem but i can't change the dataype now as data is already entered.
select ED.class,ED.CurrentOfficePlace,ED.DOB,ED.DOJInCurrentOff,ED.DOJInGovrService,ED.DOJInSamvarg,ED.EmpName,ED.HomePlace,ED.Qualification from tbl_EmplyeesBiodata ED order by DOJInGovrService asc
Date entered is in format 28-08-2004
please help me
Cast the nvarchar value to datetime and sort it...
order by CONVERT(datetime, DOJInGovrService , 102) asc
102 is the code for the datetime format - see options here: https://msdn.microsoft.com/en-us/library/ms187928.aspx[^]
If yourDOJInGovrServiceis a DATE or DATETIME column, that that will work.
But...your comment "Date entered is in format 28-08-2004" implies that it's stored as a string. If so, the best solution is for you to fix your DB first, and replace all columns which are stored as strings with the most appropriate data type: DATE, DATETIME, INT, and so on - continuing the way you are will only lead to massive complication in your SQL queries.
Strings are sorted on a character-by-character basis, so the first different character decides the order. You don't need that, because that is useless for dates. Unfortunately, while it is possible to convert the string date to a DATE in the query (but not necessarily reliable, depending on how many coders and / or users have inserted or updated the DB) it's a problem that will give you hassle from now until you leave the company.
Change your DB now, while the code base is small, and the data is also small! If you wait, the problem will become a major change. A small change now, and your current problem will go away, and a whole lot of future problems as well.
Here is my example, hope this help you a bit.
My table is:
Id(int) SortFields(varchar(50)) 1 28-08-2004 2 29-08-2004 3 01-01-2001 4 15-11-2000
SQL query:
SELECT * FROM (SELECT [Simply].Id, CONVERT(datetime, [Simply].SortFields, 105) AS FDate FROM [dbo].[Simply]) AS tb ORDER BY tb.FDate ASC
And result:
Id FDate 4 2000-11-15 00:00:00.000 3 2001-01-01 00:00:00.000 1 2004-08-28 00:00:00.000 2 2004-08-29 00:00:00.000
这篇关于如何按日期字段按升序排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
