"Volker Schmidt" wrote:

> Hi,
>
> I like to calculate an unix- timestamp column as a persistand computed
> datetime column to be able to create an index on this attribute.
>
>
> If I use the command:
>
> alter table grade add  TS_create_Datetime
> As DATEADD(s, ts_create+2*60*60, '19700101') Persistand
>
> I get:
> > Msg 4936, Level 16, State 1, Line 1
> > Computed column 'TS_create_Datetime' in table 'grade' cannot be persisted
> > because the column is non-deterministic.
>
> Does anybody know a deterministic function for conversion a Unix timestamp
> or is it possible to modify the existing function?
> I even not understand why the function does not deliver every time the same
> result...???
>
> Thanks and best regards
>
>   Volker
>
> Env:
> W2003 eng, SQL2005 SP2 eng
>
> easy:
> CREATE TABLE [dbo].[grade](
>  [ts_create] [int] NOT NULL
> ) ON [PRIMARY]
>
> from ts_create = 1156326631 should be TS_create_Datetime =8/23/2006 11:50:31
> AM
> the result
>
>
>

"Gilberto Zampatti" <GilbertoZampa***@discussions.microsoft.com> schrieb im
Newsbeitrag news:84CA03E9-A36A-45DF-9B95-571BC7E3128C@microsoft.com...
> The function DATEADD as you used it in your example uses an implicit
> conversion well described in the following note from the Books On Line:
> ---
> When you refer to datetime and smalldatetime string literals in indexed
> computed columns in SQL Server 2005, we recommend that you explicitly
> convert
> the literal to the date type that you want by using a deterministic date
> format style. For a list of the date format styles that are deterministic,
> see CAST and CONVERT. Expressions that involve implicit conversion of
> character strings to datetime or smalldatetime are considered
> nondeterministic, unless the database compatibility level is set to 80 or
> earlier. This is because the results depend on the LANGUAGE and DATEFORMAT
> settings of the server session. For example, the results of the expression
> CONVERT (datetime, '30 listopad 1996', 113) depend on the LANGUAGE setting
> because the string '30 listopad 1996' means different months in different
> languages. Similarly, in the expression DATEADD(mm,3,'2000-12-01'), the
> Database Engine interprets the string '2000-12-01' based on the DATEFORMAT
> setting.
> ---
> You should try to generate your timestamp in some other way.
>
> the Books On line chapter to be read is:
> SQL Server Database Engine -> Designing and Creating Databases ->
> Indexes ->
> Creating Indexes -> Creating Indexes on Computed Columns
>
> Gilberto
>
> "Volker Schmidt" wrote:
>
>> Hi,
>>
>> I like to calculate an unix- timestamp column as a persistand computed
>> datetime column to be able to create an index on this attribute.
>>
>>
>> If I use the command:
>>
>> alter table grade add  TS_create_Datetime
>> As DATEADD(s, ts_create+2*60*60, '19700101') Persistand
>>
>> I get:
>> > Msg 4936, Level 16, State 1, Line 1
>> > Computed column 'TS_create_Datetime' in table 'grade' cannot be
>> > persisted
>> > because the column is non-deterministic.
>>
>> Does anybody know a deterministic function for conversion a Unix
>> timestamp
>> or is it possible to modify the existing function?
>> I even not understand why the function does not deliver every time the
>> same
>> result...???
>>
>> Thanks and best regards
>>
>>   Volker
>>
>> Env:
>> W2003 eng, SQL2005 SP2 eng
>>
>> easy:
>> CREATE TABLE [dbo].[grade](
>>  [ts_create] [int] NOT NULL
>> ) ON [PRIMARY]
>>
>> from ts_create = 1156326631 should be TS_create_Datetime =8/23/2006
>> 11:50:31
>> AM
>> the result
>>
>>
>>