Why is [date] + ([time] - [offset]) not defined in SQL Server 2008?

Question

I am trying to do the following for an IIS log table:

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

However, SQL Server 2008 tells me:

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

The table indicates this definition:

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

Why is this not determined?

I really need to index this field. There are currently 1598170 rows in the table, and it is painful to ask if we cannot complete the index search. Since this is UNION'd with some other log formats, we cannot just simply use the two columns separately.

Answer 1

Your '1900-01-01' not deterministic because it depends on the language settings. of course, this is unambiguous for the DMY or MDY settings bit, in general it is ambiguous

Try '19000101' : SQL Server treats dates and times somewhat oddly: "yyyy-mm-dd" can be thought of as "yyyy-dd-mm" if you have British settings, even though there is theory in theory

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

Edit2: January 1, 1900 in datetime formats is zero, so there is no need to subtract it. Can you post sample data and display it?

Answer 2

Ok, I figured this out thanks to @ gbn's answer.

The following three equivalents:

 SELECT TOP 100 [date] + ([time] - '19000101'), [date] + ([time] - 0), [date] + [time] FROM dbo.[W3CLog] 

The bottom is determined.