更多日期格式悲伤 [英] More date format grief
问题描述
我正在调查客户在我们的数据库中报告的错误
抽象层，它让我非常不开心。
简要总结：
我有一个数据库抽象层，旨在使用JDBC在webapps和任意数据库后端之间调解
。我非常不愿意为特定的
数据库编写特殊情况代码。我们的代码与许多数据库一起令人满意地工作，
包括许多实例MS SQLServer 2000数据库使用
com.microsoft.sqlserver.SQLServerDriver。
但是，在这种情况下，数据库不接受日期。它不希望
接受java.sql.Date.toString（）格式的日期（这是ANSI
SQL 92格式）并且它不会接受ISO8601格式的日期，如果他们
有一个区域偏移（在一般情况下他们这样做） - 即使那个
区域偏移是''Z''。
通过阅读Usenet，我发现SQL Server没有默认的
日期格式。此外，它没有采用它的日期格式来自
Windows区域设置。
那么如何，为了上帝和小鱼的爱，我是否说服SQL
服务器数据库永久接受ANSI SQL 92日期，而不是基于每个会话
？
-
si***@jasmine.org.uk （Simon Brooke） http://www.jasmine.org.uk/~simon/
;;总而言之，你只需再次点击通话
;; - Minke Bouyed
Simon，
我会同意这是非常令人沮丧的，但是没有
简单的答案，因为
表示日期时间值没有国际标准。 ISO-8601有很多选项，而且SQL Server至少接受了一对ISO-8601替代品的价值。
如果您在数据中有时区信息并且想要一个适用于所有后端的产品，那么您可能会遇到麻烦。并非所有产品都支持时区，因此您的
数据最终会在不同产品上显示不同的值。
如果您想要一致性，请消除或转换
前端的时区数据，并发送每个后端你
连接到日期时间值作为表格的字符串
''YYYYMMDD HH：MM：SS' '（秒可选或小数
秒）。
我以为你也可以用''{d YYYY-MM-DD}''并且无论设置如何都会工作
（不像''YYYY-MM-DD''，它取决于日期格式设置上的
）。但是不确定最后一点。
SQL Server是否有默认的日期格式？这是几个问题：
问：SQL Server是否以
一致的方式将日期显示为字符串？
A. SQL Server没有显示任何内容。 IDE和前端确实如此。
问：SQL Server CAST是否可以使用一致的格式发送到字符串？
A.否。这取决于语言设置。
问：SQL Server能否将日期转换为格式一致的字符串？
A.是的，使用CONVERT（varchar ...，< format> ;）和字符串函数。
问：SQL Server是否正确导入每个ISO-8601允许的日期？
A.否。它确实导入了一些正确而一致的：
YYYYMMDD HH：MM：SS.fff和YYYY-MM-DDTHH：MM：SS.fff例如。
据我所知，没有时区支持。
Q. CONVERT（datetime，...）格式代码是否一致转换
？
A.不。文档没有说明这一点，但
所有数字，分隔格式除了ISO
格式，T取决于连接'的语言或
dateformat设置（dateformat覆盖语言，我相信ve）。
为什么SQL Server不能始终如一地转换SQL-92日期字符串？
好问题。它正确转换SQL-92时间戳（没有时区）
，但不是仅限日期。
如果转换时的日期格式，它将会是mdy，
ymd，或myd，但我不认为这是一个很好的解决方案。
什么是最安全的日期格式使用？
可能''YYYYMMDD HH：MM：SS。[fff]''，ISO格式，因为如果
有人将其截断到日期，它不会破坏，比如
SQL-92时间戳表格。
- Steve Kass
- 德鲁大学
- 参考号：4BA55F69-6565-4B87-BB19-E223787FDB91
Simon Brooke写道：I我正在调查客户在我们的数据库抽象层中报告的一个错误，这让我很不高兴。
摘要：
我有一个数据库抽象用于使用JDBC在webapps和任意数据库后端之间进行调解的层。我非常不愿意为特定的数据库编写特例代码。我们的代码与许多数据库一起令人满意地工作，包括使用
com.microsoft.sqlserver.SQLServerDriver的许多实例MS SQLServer 2000数据库。
然而，在这个例子中，数据库不接受约会。它不会接受java.sql.Date.toString（）格式的日期（这是ANSI
SQL 92格式），如果它们不接受ISO8601格式的日期
有区域偏移（在一般情况下它们都有） - 即使
区域偏移是Z。
我发现，通过阅读Usenet， SQL Server没有默认的日期格式。此外，它不会从Windows区域设置中采用它的日期格式。
那么，为了上帝和小鱼的爱，我如何说服SQL
服务器数据库永久接受ANSI SQL 92日期，而不是基于每个会话？
Steve Kass < SK *** @ drew.edu>写道：
Simon Brooke写道：我有一个数据库抽象层，旨在使用JDBC在webapps和任意数据库后端之间进行调解。我非常不愿意为特定的数据库编写特例代码。我们的代码与许多数据库一起令人满意地工作，包括使用
com.microsoft.sqlserver.SQLServerDriver的许多MS SQLServer 2000数据库实例。
但是，在这种情况下，数据库不接受日期。它不会接受java.sql.Date.toString（）格式的日期（这是ANSI
SQL 92格式），如果它们不接受ISO8601格式的日期
有一个区域偏移（在一般情况下它们都有） - 即使
区域偏移是''Z''。
我通过在Usenet上阅读，发现SQL Server没有没有默认的日期格式。此外，它不会采用来自Windows区域设置的日期格式。那么，为了对上帝和小鱼的爱，我是否说服SQL服务器数据库永久地接受ANSI SQL 92日期，而不是每个会话？
Q. SQL Server是否正确导入每个ISO-8601允许的日期？
答：不会。它确实正确且一致地导入了一些日期：
YYYYMMDD HH：MM：SS .fff和YYYY-MM-DDTHH：MM：SS.fff例如。
是的，但实际上，这不是有效的ISO-8601格式，因为它不会包含时区。\\ b
不包含时区。此外，我没有奢侈的
能够为每个数据库生成自定义代码。当然它必须是_possible_来说服SQL Server符合ANSI 92吗？
为什么SQL Server不能始终如一地转换SQL-92日期字符串？
好问题。它正确转换SQL-92时间戳（没有时区）
，但不是仅限日期。
不，它没有。这就是所有这些悲伤开始的地方：我们已经将这些内容发送到SQL Server多年以及其他所有安装
它已经有效了，但现在我们有了使用MS SQL Server 2000的客户
在他们的一个盒子上一直重复地失败
（他们有另一个盒子运行相同的软件而不是它/>
失败了，在我们的盒子里，我们每个人都可以做到这一点
相同它并没有失败）。我已经尽我所能找到了盒子之间设置的差异，到目前为止我已经失败了。
-
si***@jasmine.org.uk （Simon Brooke） http://www.jasmine.org.uk/~simon/
;;总而言之，你只需再次点击通话
;; - Minke Bouyed
Simon Brooke写道：Steve Kass< sk ** *@drew.edu>写道：
Simon Brooke写道：
我有一个数据库抽象层，用于调解
使用JDBC的webapps和任意数据库后端。我非常不愿意为特定的数据库编写特例代码。我们的代码与许多数据库一起令人满意地工作，包括使用
com.microsoft.sqlserver.SQLServerDriver的许多实例MS SQLServer 2000数据库。
但是，在这种情况下，数据库不会接受日期。它不会接受java.sql.Date.toString（）格式的日期（这是ANSI
SQL 92格式），如果它们不接受ISO8601格式的日期
有一个区域偏移（在一般情况下它们都有） - 即使
区域偏移是''Z''。
我通过在Usenet上阅读，发现SQL Server没有没有默认的日期格式。此外，它不会采用来自Windows区域设置的日期格式。那么，为了对上帝和小鱼的爱，我是否说服SQL服务器数据库永久地接受ANSI SQL 92日期，而不是每个会话？
Q. SQL Server是否正确导入每个ISO-8601允许的日期？
A.不会。它确实正确且一致地导入其中一些：
YYYYMMDD HH：MM：SS.fff和YYYY-MM-DDTHH：MM：SS.fff例如。
是的，但实际上，这不是有效的ISO-8601格式，因为它不包含时区。此外，我没有能够为每个数据库生成自定义代码的奢侈。当然它必须_possible_说服SQL Server符合ANSI 92？
我的参考是ISO8601：2000E（2000年12月），我没看到
需要时区。你有章节号吗？
第5.4节描述了时间点表示，并说如果使用的话，
区域指示符是空的当地时间
天依照...，参考前面提供的部分
报价hhmm，hh：mm，hhmmss，hh：mm： ss，hh：mm，m，hhmm，m，hh等，
等，可能的日期格式。
它还提供Basic（没有连字符）和扩展（用连字符）格式
的所有内容，没有我可以看到强制一个或
其他。如果SQL Server全部理解它们会很好，但是它确实理解带有连字符和T的那个（如果没有模糊结果，ISO允许T省略
）虽然我无法看到
对于其他ISO格式有什么意义 - 可能错过了一些东西
疯狂就像BC年份使用T的周数一样疯狂。）
SQL Server也理解没有连字符或T的那个。
在ISO中看起来可以省略日期分隔符但包括时间
分隔符。为什么SQL Server不能一致地转换SQL-92日期字符串？
好问题。它正确地转换SQL-92时间戳（没有时区）
，但不是仅限日期。
不，它不是。这就是所有这些悲伤开始的地方：我们已经将它发送到SQL Server多年以及其他所有安装它已经有效，但现在我们有一个客户使用MS SQL Server 2000 <在他们的一个盒子上一直重复地失败了（他们有另一个盒子运行相同的软件，它没有失败，在我们的盒子上我们有一个可能的每一个可能使
相同，它不会失败）。我已尽我所能找到盒子之间设置的差异，到目前为止我失败了。
我的单据。 SQL Server不懂SQL-92
TIMESTAMP''2003-02-22 23：34：43.123''，如
CAST（TIMESTAMP''2003-02-22 23：34：43.123''作为DATETIME）
但我怀疑你是建筑CAST（TIMESTAMP ......
表达式.SQL Server使用
{ts''1996-12-19 11：11：11.000''}表示
a时间戳文字，以及我明白地解释它，
，因为它的日期文字格式为
{d''1996-12-19''}
如果没有{ts ...}，这些字符串就像所有
数字分隔日期格式一样，当含蓄地将
转换为日期时
d和m在dateformat设置中的相对位置实际上隐含了
来自语言选择或明确设置。
set dateformat dmy
go
声明@d datetime
set @d = {ts''1996-12-19 11：11：11.000 '}
选择@d
go
声明@d datetime
set @d = {d'' 1996-12-19''}
选择@d
go
声明@d datetime
set @ d =''1996-12-19 11：11：11.000''
选择@d
go
声明@d datetime
设置@d =''1996-12-19''
选择@d
我不知道为什么你如果
设置正确，可能会有问题，但可能有一些驱动程序参数
埋在注册表中，或者其他一些设置不是
很明显。也许有人想要us_english但dmy，并且
得到了修改syslanguages表的好主意！
这个bum服务器错误吗？ />
设置dateformat dmy
声明@d datetime
set @d =''2003-02-19''
如果服务器安装为us_english，并且没有人
更改了dateformat设置或修改了syslanguages，
我认为它应该可以工作并且可能是一个案例对于产品
支持。另一方面，我不会想要一个依赖于安装语言的产品。
SK
I''m investigating a bug a customer has reported in our database
abstraction layer, and it''s making me very unhappy.
Brief summary:
I have a database abstraction layer which is intended to mediate
between webapps and arbitrary database backends using JDBC. I am very
unwilling indeed to write special-case code for particular
databases. Our code has worked satisfactorily with many databases,
including many instances MS SQLServer 2000 databases using the
com.microsoft.sqlserver.SQLServerDriver.
However, in this instance, the database won''t accept dates. It won''t
accept dates in the java.sql.Date.toString() format (which is the ANSI
SQL 92 format) and it won''t accept dates in the ISO8601 format if they
have a zone offset (which in the general case they do) - even if that
zone offset is ''Z''.
I find, by reading on Usenet, that SQL Server doesn''t have a default
date format. Furthermore, it doesn''t take it''s date format from
Windows Regional settings.
So how, for the love of God and Little Fishes, do I persuade a SQL
Server database to accept ANSI SQL 92 dates, permanently, not on a
per-session basis?
--
si***@jasmine.org.uk (Simon Brooke) http://www.jasmine.org.uk/~simon/
;; all in all you''re just another click in the call
;; -- Minke Bouyed
Simon,
I''ll agree this is very frustrating, but there is no
easy answer, since there is no international standard for
representation of datetime values. ISO-8601 has a huge
number of options, and SQL Server accepts at least a couple
of the ISO-8601 alternatives.
If you have timezone information in data and want one
product that works with all back ends, then you''ve probably
got trouble. Not all products support timezones, so your
data will end up with different values on different products.
If you want consistency, either eliminate or convert the
timezone data in your front end, and send every back end you
connect to datetime values as strings of the form
''YYYYMMDD HH:MM:SS'' (seconds optional or with fractional
seconds as well).
I thought you could also use ''{d YYYY-MM-DD}'' and it would
work regardless of settings (unlike ''YYYY-MM-DD'' which depends
on date format settings). Not sure about this last bit, though.
Does SQL Server have a default date format? This is several questions:
Q. Does SQL Server display dates as character strings in a
consistent way?
A. SQL Server doesn''t display anything. IDEs and front-ends do.
Q. Does SQL Server CAST dates to strings with a consistent format?
A. No. This depends on language settings.
Q. Can SQL Server convert dates to strings with a consistent format?
A. Yes, with CONVERT(varchar..., <format>) and string functions.
Q. Does SQL Server import every ISO-8601-allowed date correctly?
A. No. It does import a few of them correctly and consistently:
YYYYMMDD HH:MM:SS.fff and YYYY-MM-DDTHH:MM:SS.fff for example.
As far as I know, there is no timezone support.
Q. Does CONVERT(datetime, ...) with format codes convert
consistently?
A. No. The documentation does not make this clear, but
all the numerical, delimited formats except for the ISO
format with the T depend on the connection''s language or
dateformat setting (dateformat overrides language, I believe).
Why doesn''t SQL Server consistently convert SQL-92 date strings?
Good question. It converts SQL-92 timestamp (without timezone)
correctly, but not date-only.
It will, if the date format at the time of conversion is mdy,
ymd, or myd, but I don''t think that''s a great solution.
What''s the safest date format to use?
Probably ''YYYYMMDD HH:MM:SS.[fff]'', an ISO format, since if
someone truncates it to date-only, it won''t break, like the
SQL-92 timestamp form.
-- Steve Kass
-- Drew University
-- Ref: 4BA55F69-6565-4B87-BB19-E223787FDB91
Simon Brooke wrote:I''m investigating a bug a customer has reported in our database
abstraction layer, and it''s making me very unhappy.
Brief summary:
I have a database abstraction layer which is intended to mediate
between webapps and arbitrary database backends using JDBC. I am very
unwilling indeed to write special-case code for particular
databases. Our code has worked satisfactorily with many databases,
including many instances MS SQLServer 2000 databases using the
com.microsoft.sqlserver.SQLServerDriver.
However, in this instance, the database won''t accept dates. It won''t
accept dates in the java.sql.Date.toString() format (which is the ANSI
SQL 92 format) and it won''t accept dates in the ISO8601 format if they
have a zone offset (which in the general case they do) - even if that
zone offset is ''Z''.
I find, by reading on Usenet, that SQL Server doesn''t have a default
date format. Furthermore, it doesn''t take it''s date format from
Windows Regional settings.
So how, for the love of God and Little Fishes, do I persuade a SQL
Server database to accept ANSI SQL 92 dates, permanently, not on a
per-session basis?
Steve Kass <sk***@drew.edu> writes:
Simon Brooke wrote:I have a database abstraction layer which is intended to mediate
between webapps and arbitrary database backends using JDBC. I am very
unwilling indeed to write special-case code for particular
databases. Our code has worked satisfactorily with many databases,
including many instances MS SQLServer 2000 databases using the
com.microsoft.sqlserver.SQLServerDriver. However, in this instance, the database won''t accept dates. It won''t
accept dates in the java.sql.Date.toString() format (which is the ANSI
SQL 92 format) and it won''t accept dates in the ISO8601 format if they
have a zone offset (which in the general case they do) - even if that
zone offset is ''Z''.
I find, by reading on Usenet, that SQL Server doesn''t have a default
date format. Furthermore, it doesn''t take it''s date format from
Windows Regional settings. So how, for the love of God and Little
Fishes, do I persuade a SQL
Server database to accept ANSI SQL 92 dates, permanently, not on a
per-session basis?Q. Does SQL Server import every ISO-8601-allowed date correctly?
A. No. It does import a few of them correctly and consistently:
YYYYMMDD HH:MM:SS.fff and YYYY-MM-DDTHH:MM:SS.fff for example.
Yes, but, actually, that''s not a valid ISO-8601 format, because it
doesn''t include a timezone. Furthermore, I don''t have the luxury of
being able to generate custom code for every database. Surely it must
be _possible_ to persuade SQL Server to conform to ANSI 92?
Why doesn''t SQL Server consistently convert SQL-92 date strings?
Good question. It converts SQL-92 timestamp (without timezone)
correctly, but not date-only.
No, it doesn''t. That is where all this grief started: we''ve been
sending that to SQL Server for years and in every other installation
it has worked, but now we have a customer using MS SQL Server 2000 who
is having that fail consistently and repeatedly on one of their boxes
(they have another box running identical software on which it is not
failing, and on our box which we''ve one everythintg possible to make
identical it doesn''t fail). I''ve done everything I can to find a
difference in setup between the boxes and so far I''ve failed.
--
si***@jasmine.org.uk (Simon Brooke) http://www.jasmine.org.uk/~simon/
;; all in all you''re just another click in the call
;; -- Minke Bouyed
Simon Brooke wrote:Steve Kass <sk***@drew.edu> writes:Simon Brooke wrote:I have a database abstraction layer which is intended to mediate
between webapps and arbitrary database backends using JDBC. I am very
unwilling indeed to write special-case code for particular
databases. Our code has worked satisfactorily with many databases,
including many instances MS SQLServer 2000 databases using the
com.microsoft.sqlserver.SQLServerDriver.
However, in this instance, the database won''t accept dates. It won''t
accept dates in the java.sql.Date.toString() format (which is the ANSI
SQL 92 format) and it won''t accept dates in the ISO8601 format if they
have a zone offset (which in the general case they do) - even if that
zone offset is ''Z''.
I find, by reading on Usenet, that SQL Server doesn''t have a default
date format. Furthermore, it doesn''t take it''s date format from
Windows Regional settings. So how, for the love of God and Little
Fishes, do I persuade a SQL
Server database to accept ANSI SQL 92 dates, permanently, not on a
per-session basis?
Q. Does SQL Server import every ISO-8601-allowed date correctly?
A. No. It does import a few of them correctly and consistently:
YYYYMMDD HH:MM:SS.fff and YYYY-MM-DDTHH:MM:SS.fff for example.
Yes, but, actually, that''s not a valid ISO-8601 format, because it
doesn''t include a timezone. Furthermore, I don''t have the luxury of
being able to generate custom code for every database. Surely it must
be _possible_ to persuade SQL Server to conform to ANSI 92?
My reference is ISO8601:2000E (December, 2000), and I don''t see
where a timezone is required. Do you have the paragraph number?
Section 5.4 describes point-in-time representations, and says "The
zone designator is empty if use is made of the local time of the
day in accordance...", referring to earlier sections that give
offer hhmm, hh:mm, hhmmss, hh:mm:ss, hh:mm,m, hhmm,m, hh, etc.,
etc., as possible date formats.
It also gives Basic (no hyphens) and extended (with hyphens) formats
for everything, without as far as I can see mandating one or the
other. It would be nice if SQL Server understood them all, but it
does understand the one with hyphens and a T (ISO allows the T to be
omitted if no ambiguity results, though I couldn''t see where
any would regarding other ISO formats - probably missed something
crazy like week numbers in BC years that used a T.)
SQL Server also understands the one with no hyphens or T.
It looks ok in ISO to omit date separators but include time
separators.Why doesn''t SQL Server consistently convert SQL-92 date strings?
Good question. It converts SQL-92 timestamp (without timezone)
correctly, but not date-only.
No, it doesn''t. That is where all this grief started: we''ve been
sending that to SQL Server for years and in every other installation
it has worked, but now we have a customer using MS SQL Server 2000 who
is having that fail consistently and repeatedly on one of their boxes
(they have another box running identical software on which it is not
failing, and on our box which we''ve one everythintg possible to make
identical it doesn''t fail). I''ve done everything I can to find a
difference in setup between the boxes and so far I''ve failed.
My slip. SQL Server doesn''t understand SQL-92
TIMESTAMP ''2003-02-22 23:34:43.123'' at all, as in
CAST(TIMESTAMP ''2003-02-22 23:34:43.123'' as DATETIME)
but I doubt you are construction CAST(TIMESTAMP ...
expressions. SQL Server uses
{ts ''1996-12-19 11:11:11.000''} to represent
a timestamp literal, and interprets it unambiguously,
as far as I know, as it does the date literal format of
{d ''1996-12-19''}
Without the {ts ... }, these strings alone, like all
numeric delimited date formats, when implicitely
converted to dates follow the relative positions of
d and m in the dateformat setting in effect implicit
from the language selection or explicitly set.
set dateformat dmy
go
declare @d datetime
set @d = {ts ''1996-12-19 11:11:11.000''}
select @d
go
declare @d datetime
set @d = {d ''1996-12-19''}
select @d
go
declare @d datetime
set @d = ''1996-12-19 11:11:11.000''
select @d
go
declare @d datetime
set @d = ''1996-12-19''
select @d
I don''t know why you would have trouble with this if the
settings were right, but maybe there''s some driver parameter
buried in the registry, or some other setting that''s not
obvious. Perhaps someone wanted us_english but dmy, and
got the bright idea of modifying the syslanguages table!
Does that bum server error out on this??
set dateformat dmy
declare @d datetime
set @d = ''2003-02-19''
If the server is installed as us_english, and no one
has changed the dateformat setting or modified syslanguages,
I think it should work and might be a case for product
support. On the other hand, I wouldn''t
want a product that depended on the language of installation.
SK
这篇关于更多日期格式悲伤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
