转换 SQL 日期格式的结果不一样 [英] Converting SQL date formats not ending up the same

问题描述

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

I am trying to remove the timestamp from my date values so I end up with something like mm/dd/yy or mm/dd/yyyy. I referenced many technet, stackoverflow, and w3schools articles but still can't get the date to appear correctly. All of my columns in the table are defined as a datetime and they come from the same table.

我正在使用这样的转换语句:CONVERT(VARCHAR(10), E.PD_DT, 101) AS 'Paid Date'

I am using convert statements like this: CONVERT(VARCHAR(10), E.PD_DT, 101) AS 'Paid Date'

我使用了 10 和 11 代替了 101，但数据仍然存在相同的问题(如下).发生的情况是，当日期值(不包括时间)为 8 个字符时，我从时间中获得了一个额外的字符，如 Claim Adjustment Date-10 和 Claim Adjustment Date-11 列中所示.这是我的数据:

In place of 101 I have used 10 and 11, still have the same issue with the data (below). What's happening is when the date value (not including the time) is 8 characters, I am getting an additional character from the time, as seen in the Claim Adjustment Date-10 and Claim Adjustment Date-11 columns. Here is my data:

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

奇怪的是，如果月份或日期小于等于Claim Paid Date"列中的所有日期，则所有的日期都有一个零用于填充.10. 这使得转换出来的效果很好，但月或日是 <10 并且没有零是我遇到问题的地方.

The strange thing is that all of the dates in the "Claim Paid Date" column have a zero for padding if the month or day is < 10. This makes the convert come out just fine but where the month or day is < 10 and doens't have a zero is where I get my problem.

推荐答案

正确的做法是首先将数据存储为 DATETIME.目前，您将数据存储为字符串，出于多种原因，您不应该这样做:

The correct way to do this is to store the data as DATETIME in the first place. Currently you are storing the data as strings, and you shouldn't be doing this, for a variety of reasons:

• 您失去了排序能力(9/1/2012 将在 12/12/2012 之后排序)而不先转换.转换成本很高.
• 如果不先转换，您将无法执行有意义的范围查询(同样，9/1/2012 > 12/12/2012)
• 您失去了内置验证的所有手段 - 任何人都可以输入 13/33/2999 或 02/32/2099 或简单地输入 foo 作为日期"
• 如果不先转换，您将无法对这些列执行与日期相关的函数，例如 DATEADD、DATEPART 或 DATEDIFF
• 您需要先转换为另一种类型，然后才能执行转换，这将允许您执行诸如修剪时间或以特定格式显示日期之类的操作

• you lose the ability to sort (9/1/2012 will sort after 12/12/2012) without converting first. Converting is expensive.
• you lose the ability to perform meaningful range queries without converting first (again, 9/1/2012 > 12/12/2012)
• you lose all means of built-in validation - anyone can enter 13/33/2999 or 02/32/2099 or simply foo as a "date"
• you lose the ability to perform date-related functions such as DATEADD, DATEPART or DATEDIFF against these columns without first converting
• you need to convert to another type first before you can perform a conversion that will allow you to do things like trim time or present the date in a specific format

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

If you can't fix the table design and want to continue storing dates using the wrong data type, then the next best thing is to simply format the string in the client. If the client knows it's a date/time, then using .Format() or .ToString() should allow you to present the date without the time in whatever format you want.

您应该以明确的格式向用户显示日期，因为您永远不知道您的某些读者何时会看到 6/12/2012 并且不确定这是否是6 月 12 日或 12 月 6 日(例如，加拿大人和美国人会以不同的方式看待这些).所以你可以使用，作为一个例子:

You should be presenting dates to users in unambiguous formats, since you never know when some of your readers will see 6/12/2012 and not be sure if that's June 12 or December 6 (Canadians and Americans will see those differently, for example). So you can use, as an example:

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

这将呈现一个明确的日期，如 2012-06-12，它只会在法国一些非常孤立的地区和年龄段(y-d-m 似乎仍然流行)中被误解.

This will present an unambiguous date like 2012-06-12, which can only be misinterpreted in some very isolated regions and age groups in France (where y-d-m seems to remain in vogue).

但是，如果您绝对想呈现模棱两可的格式，则可以使用:

However, if you absolutely want to present the ambiguous format, you can just use:

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

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

...with no reason to change your SQL query at all. If, for some reason, you absolutely want or need to do this at the query level, then you can use:

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

如果你想使用不那么模糊的形式，比如 yyyymmdd 或 yyyy-mm-dd，你可以使用:

If you want to use less ambiguous forms, like yyyymmdd or yyyy-mm-dd, you can use:

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

但同样，数据库层的转换成本很高，而客户端层的字符串格式化(您已经在最后一步将这些内容作为字符串处理)相对便宜.在进入和离开数据库的过程中，您应该尽可能长时间地将这些作为日期值保留.在涉及 SQL Server 的任何地方将它们视为字符串都没有任何优势.

But again, conversion at the database layer is expensive, whereas string formatting at the client layer (where you're already handling these things as strings at that final step) is relatively cheap. You should be keeping these as date values as long as possible, both on the way in and on the way out of the database. There is just no advantage to treating them as strings anywhere SQL Server is concerned.

总结:

1. 修理桌子
2. 在客户端执行转换(最好是明确的格式)
3. 如果您无法在客户端执行转换，请使用双嵌套的 CONVERT(同样，最好使用明确的格式)

1. fix the table
2. perform the conversion on the client (preferably to an unambiguous format)
3. if you can't perform the conversion on the client, use a double-nested CONVERT (again, preferably to an unambiguous format)

这篇关于转换 SQL 日期格式的结果不一样的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！