为什么在SQL Server 2008中[日期] +([时间]-[偏移量])是不确定的? [英] Why is [date] + ([time] - [offset]) non-deterministic in SQL Server 2008?

问题描述

我正在为IIS日志表执行以下操作:

I'm trying to do the following for my IIS logs table:

ALTER TABLE [W3CLog]
ADD [LogTime] AS [date] + ([time] - '1900-01-01') PERSISTED

但是，SQL Server 2008告诉我:

However, SQL Server 2008 tells me:

Computed column 'LogTime' in table 'W3CLog' cannot be persisted because the column is non-deterministic.

该表具有以下定义:

CREATE TABLE [dbo].[W3CLog](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    ...
    [date] [datetime] NULL,
    [time] [datetime] NULL,
    ...
)

为什么不确定性如此?

我真的需要索引该字段.该表当前有1598170行，如果无法全时进行索引搜索，查询起来会很麻烦.由于这与其他一些日志格式实现了统一，所以我们不能非常轻松地单独使用这两列.

I really need to index that field. The table currently has 1598170 rows, and it is a pain to query if we can't do an index seek on the full time. Since this is being UNION'd with some other log formats, we can't very easily just use the two columns separately.

推荐答案

您的'1900-01-01'是不确定的，因为它取决于语言设置.当然，这对于DMY或MDY设置来说是明确的，通常它是模棱两可的

Your '1900-01-01' is non-deterministic because it depends on language settings. of course, this is unambiguous for DMY or MDY settings bit generally it's ambiguous

尝试'19000101':SQL Server对日期和时间的处理有些奇怪:尽管理论上是ISO的，但如果您具有英国设置，则可以将"yyyy-dd-mm"视为"yyyy-dd-mm"

Try '19000101': SQL Server treats dates and times somewhat oddly: "yyyy-mm-dd" can be treated as "yyyy-dd-mm" if you have British settings despite being ISO in theory

使用它删除日期方面:DATEADD(day, 0, DATEDIFF(day, 0, [time]))

use this to remove the date aspect: DATEADD(day, 0, DATEDIFF(day, 0, [time]))

Edit2:1900年1月1日在日期时间格式中为零，因此无需将其减去.您可以张贴示例数据并输出吗?

1st Jan 1900 is zero in the datetime formats, so no need to subtract it. Can you post sample data and output please?

这篇关于为什么在SQL Server 2008中[日期] +([时间]-[偏移量])是不确定的?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！