如何在 SQL Server 2000 中对 Varchar 日期进行排序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4905884/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to sort Varchar Date in SQL Server 2000
提问by mahesh
My table schema (table Name Stock)
我的表架构（表名称股票）
Field
场地
Date Datetime
If Datehaving datatype is Datetimethen SQL Server sorts the date in well/perfect order. See Below Example.
如果Date具有数据类型，Datetime则 SQL Server 以良好/完美的顺序对日期进行排序。请参见下面的示例。
select date from stock order by date
Then result is:
那么结果是：
Date(yyyy/MM/dd)
2010-04-02 00:00:00.000
2011-02-02 00:00:00.000
2011-02-03 00:00:00.000
2011-02-03 00:00:00.000
But if I am going to sort the same by casting varchar than it will be ultimately create problem and the problem is it will not sort date by date order as it is now casting in Varchar.
但是，如果我要通过强制转换 varchar 对其进行排序，那么最终会产生问题，问题是它不会像现在在 Varchar 中强制转换那样按日期顺序对日期进行排序。
Look at example below:
看下面的例子：
select date = convert(varchar, date, 103) from stock order by date
Then result is:
那么结果是：
Date(dd/MM/yyyy)
02/02/2011
02/04/2010
03/02/2011
03/02/2011
You can see second query's result that it will not sort date in order as because it is now not in datetime datatype. It is casting as varchar or you can say it as string.
您可以看到第二个查询的结果，它不会按顺序对日期进行排序，因为它现在不是日期时间数据类型。它被转换为 varchar 或者你可以把它说成字符串。
Now come to the point that:
现在来点：
If I want to sort date which is casting in varchar order by date than how to do it?.
如果我想按日期对按 varchar 顺序转换的日期进行排序，而不是怎么做？。
回答by marc_s
What you're doing is converting a varcharto a varcharand then sorting by that... you're not sorting by date!
你正在做的是将 a 转换varchar为 avarchar然后按那个排序……你不是按日期排序！
convert(varchar, date, 103)
*********
This type here is what you convert your value into - you're converting into a varchar- of course it won't sort by date then!
此处的这种类型是您将值转换为的 - 您正在转换为varchar- 当然它不会按日期排序！
Try this instead:
试试这个：
SELECT CONVERT(DATETIME, date, 103)
FROM dbo.Stock
ORDER BY CONVERT(DATETIME, date, 103)
NOWyou're actually converting your varcharcolumn dateto a DATETIMEvalue, and sorting on that resulting DATETIME- and now you get the output:
现在您实际上是将您的varchar列转换date为一个DATETIME值，并对结果进行排序DATETIME- 现在您得到了输出：
2010-04-02 00:00:00.000
2011-02-02 00:00:00.000
2011-02-03 00:00:00.000
2011-02-03 00:00:00.000
Update:if you need another formatting, you can of course convert your DATETIMEagain, back into a VARCHARusing CONVERTwith a different style:
更新：如果您需要另一种格式，您当然可以DATETIME再次将您的格式转换回VARCHAR使用CONVERT不同的样式：
Read all about what styles are supported in MSDN CAST and CONVERT
阅读有关MSDN CAST 和 CONVERT支持哪些样式的所有信息
SELECT
date = CONVERT(VARCHAR, CONVERT(DATETIME, date, 103), 103) -- convert back to VARCHAR
FROM
dbo.Stock
ORDER BY
CONVERT(DATETIME, date, 103) -- sort by the DATETIME !
and then you get this output:
然后你得到这个输出：
02/04/2010
02/02/2011
03/02/2011
03/02/2011
Just make sure to sort on the DATETIMEvalue! (not on a string representation of your DATETIME)
只要确保按DATETIME值排序！（不是你的字符串表示DATETIME）
And as I said: if you store your dates as DATETIME from the beginning, you'll save yourself a lot of those conversion back and forth!!
正如我所说：如果您从一开始就将日期存储为 DATETIME，那么您将节省很多来回转换的时间！！
回答by Bhavik Goyal
You can use sub query as follows
您可以使用子查询如下
select convert(varchar,date,103) date from (select date from stock order by date)p
回答by dizad87
this one worked for me:
这个对我有用：
SELECT date FROM stock ORDER BY CAST(date AS DATE)
