无法在 SQL Server 中存储某些日期时间格式 [英] Impossible to store certain datetime formats in SQL Server

问题描述

目前我有一列将时间戳保存为 datetime

At the moment I have a column that holds timestamps as a datetime

数据被存储为10/30/2011 10:50:34 AM

我的目标是将该特定表格中的每个日期转换为类似 30 Oct 2011 10:50:34

My goal is to convert every date in that specific table to be formatted like 30 Oct 2011 10:50:34

问题:

当我尝试运行此 SQL 时，它成功处理.

When I attempt to run this SQL it successfully processes.

UPDATE DatesTable 
SET DateTime = '30 Oct 2011 10:50:34' 
WHERE DateTime = '10/30/2011 10:50:34 AM'

我注意到这很奇怪，所以如果我改变 SQL 说

I noticed that was weird so if I changed the SQL to say

UPDATE DatesTable 
SET DateTime = '31 Oct 2011 10:50:34' 
WHERE DateTime = '10/30/2011 10:50:34 AM'

它使用正确的日期/时间信息更新日期时间字段，但保持相同的格式.

It updates updates the datetime field with the correct day/time information but keeps it in the same format.

我已经研究了该列的其他可能的日期数据类型，但似乎没有任何内容支持该日期.我也遇到过这个 SQL，但不确定它是否能解决我的问题.

I have looked into other possible date datatypes for that column but nothing seems to support that date. I have also come across this SQL but am unsure if it can solve my problem.

SELECT CONVERT(datetime, datecolumnname, formattingparam) as tmp FROM tablename

转换链接

推荐答案

SQL Server 不以任何字符串格式存储 DateTime - 它存储为 8 字节数值.

SQL Server doesn't store a DateTime in any string format - it's stored as an 8 byte numerical value.

各种设置(语言、日期格式)仅影响 DateTime 在 SQL Server Management Studio 中的显示方式 - 或者当您尝试将字符串转换为 日期时间.

The various settings (language, date format) only influence how the DateTime is shown to you in SQL Server Management Studio - or how it is parsed when you attempt to convert a string to a DateTime.

SQL Server 支持多种格式 - 请参阅 CAST 上的 MSDN 联机丛书和转换.大多数这些格式取决于您的设置 - 因此，这些设置有时可能有效 - 有时则无效.

There are many formats supported by SQL Server - see the MSDN Books Online on CAST and CONVERT. Most of those formats are dependent on what settings you have - therefore, these settings might work some times - and sometimes not.

解决此问题的方法是使用 SQL Server 支持的 ISO-8601 日期格式 - 此格式始终有效 - 无论您的 SQL Server 语言和日期格式设置.

The way to solve this is to use the ISO-8601 date format that is supported by SQL Server - this format works always - regardless of your SQL Server language and dateformat settings.

SQL Server 支持 ISO-8601 格式两种口味:

The ISO-8601 format is supported by SQL Server comes in two flavors:

• YYYYMMDD 仅用于日期(无时间部分)- 请注意:无破折号！，这非常重要！YYYY-MM-DD 不独立于 SQL Server 中的日期格式设置，并且不适用于所有情况！

• YYYYMMDD for just dates (no time portion) - note here: no dashes!, that's very important! YYYY-MM-DD is NOT independent of the dateformat settings in your SQL Server and will NOT work in all situations!

或:

• YYYY-MM-DDTHH:MM:SS 用于日期和时间 - 请注意:此格式有破折号.

• YYYY-MM-DDTHH:MM:SS for dates and times - note here: this format has dashes.

这对 SQL Server 2000 及更新版本有效.

This is valid for SQL Server 2000 and newer.

如果您使用 SQL Server 2008 和 DATE 数据类型(仅 DATE - not DATETIME！)，那么您确实也可以使用 YYYY-MM-DD 格式，这也适用于 SQL Server 中的任何设置.

If you use SQL Server 2008 and the DATE datatype (only DATE - not DATETIME!), then you can indeed also use the YYYY-MM-DD format and that will work, too, with any settings in your SQL Server.

不要问我为什么整个主题如此棘手且有些混乱 - 就是这样.但是使用 YYYYMMDD 格式，您应该适用于任何版本的 SQL Server 以及 SQL Server 中的任何语言和日期格式设置.

Don't ask me why this whole topic is so tricky and somewhat confusing - that's just the way it is. But with the YYYYMMDD format, you should be fine for any version of SQL Server and for any language and dateformat setting in your SQL Server.

这篇关于无法在 SQL Server 中存储某些日期时间格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！