转换SQL日期格式的结果并不相同

Question

我试图从我的日期值中删除时间戳，这样我就可以得到类似于mm/dd/yy或mm/dd/yyyy的结果。我参考了许多technet、stackoverflow和w3schools文章，但仍然无法正确显示日期。表中的所有列都被定义为datetime，它们来自同一个表。

我使用这样的convert语句: CONVERT(VARCHAR(10)，E.PD_DT，101) AS 'Paid‘

我用10和11代替了101，仍然有相同的数据问题(如下所示)。当日期值(不包括时间)是8个字符时，我将从时间中获得一个额外的字符，如索赔调整日期-10和索赔调整日期-11列中所示。以下是我的数据：

Claim Paid Date-101     Claim Paid Date     Claim Adjustment Date-10    Claim Adjustment Date-11    Claim Adjustment Date
10/23/2012              10/23/12 12:00 AM   9/4/2012 1                  9/4/2012 1                      9/4/12 12:00 AM
10/23/2012              10/23/12 12:00 AM   9/4/2012 1                  9/4/2012 1                      9/4/12 12:00 AM
10/23/2012              10/23/12 12:00 AM   9/4/2012 1                  9/4/2012 1                      9/4/12 12:00 AM
09/06/2011              09/06/11 12:00 AM   9/4/2012 1                  9/4/2012 1                      9/4/12 12:00 AM
10/23/2012              10/23/12 12:00 AM   8/21/2012                   8/21/2012                       8/21/12 12:00 AM
09/06/2011              09/06/11 12:00 AM   8/21/2012                   8/21/2012                       8/21/12 12:00 AM

奇怪的是，如果月或日小于10，则"Claim Date“列中的所有日期都有一个零作为填充。这使得转换结果很好，但月或日小于10且没有零的地方就是我的问题所在。

Answer 1

您有一个字符串源，而不是DATETIME源。

DATETIME发生了什么？

SELECT GETDATE() -- DATETIME
      ,CAST(GETDATE() AS DATE) --DATE
      ,CONVERT(VARCHAR(10),GETDATE(),101) --101 format

结果：

DateTime                Date       101 Format
----------------------- ---------- ----------
2013-09-24 13:58:48.880 2013-09-24 09/24/2013

字符串作为DATETIME巡游会发生什么？

DECLARE @fake_date VARCHAR(25) = '10/23/12 12:00 AM'
SELECT CAST(@fake_date AS DATETIME) --DATETIME
      ,CAST(@fake_date AS DATE) --DATE
      ,CONVERT(VARCHAR(10),@fake_date,101) --101 format

结果：

DateTime                Date       101 Format
----------------------- ---------- ----------
2012-10-23 00:00:00.000 2012-10-23 10/23/12 1

所以也许你想要：

CONVERT(VARCHAR(10),CAST(@fake_date AS DATE),101) 

Answer 2

正确的做法是首先将数据存储为DATETIME。当前您将数据存储为字符串，出于各种原因，您不应该这样做：

• 你失去了在不进行转换的情况下进行排序的能力(9/1/2012将在12/12/2012之后排序)。转换是昂贵的。date你失去了在不转换的情况下执行有意义的范围查询的能力(同样，9/1/2012 > 12/12/2012)
• you失去了所有内置的验证手段-任何人都可以输入13/33/2999或02/32/2099，或者简单地foo作为“date”
• 你失去了执行与日期相关的函数的能力，比如DATEADD，没有first converting
• you的这些列的DATEPART或DATEDIFF需要首先转换为另一种类型，然后才能执行允许您执行诸如裁剪时间或以特定格式显示日期等操作的转换

如果您不能修复表设计，并且希望继续使用错误的数据类型存储日期，那么下一个最好的办法就是在客户端中简单地格式化字符串。如果客户端知道这是一个日期/时间，那么使用.Format()或.ToString()应该允许您以任何想要的格式表示不带时间的日期。

你应该以明确的格式向用户展示日期，因为你永远不知道你的一些读者什么时候会看到6/12/2012，也不确定是6月12日还是12月6日(例如，加拿大人和美国人对这两个日期的看法会有所不同)。因此，您可以使用以下示例：

DateVariable.Format('yyyy-MM-dd')

这将是一个明确的日期，如2012-06-12，只有在法国一些非常孤立的地区和年龄组才会被误解(在法国，y-d-m似乎仍然很流行)。

但是，如果您绝对想要显示模棱两可的格式，您可以只使用：

DateVariable.Format('MM/dd/yyyy')

...with根本没有理由更改您的SQL查询。如果出于某种原因，您绝对希望或需要在查询级别执行此操作，则可以使用：

SELECT CONVERT(CHAR(10), CONVERT(DATE, col), 101) -- mm/dd/yyyy

如果您想使用不太模糊的表单，如yyyymmdd或yyyy-mm-dd，您可以使用：

SELECT CONVERT(CHAR(8),  CONVERT(DATE, col), 112) -- yyyymmdd
SELECT CONVERT(CHAR(10), CONVERT(DATE, col), 120) -- yyyy-mm-dd

但同样，数据库层的转换代价很高，而客户端层的字符串格式化(您已经在最后一步将这些内容作为字符串进行处理)相对较低。您应该尽可能长时间地将这些值保留为日期值，无论是在进入数据库的过程中还是在离开数据库的过程中。在SQL Server涉及到的任何地方，将它们视为字符串都没有好处。

摘要So，

1. 修复客户端上的table
2. perform转换(最好是明确的格式)
4. 如果您不能在客户端执行转换，请使用双嵌套的CONVERT (同样，最好是明确的格式)