TSQL Cannot create index on view - the view uses an implicit conversion from string to datetime

Question

SQL SERVER 2008 r2

I'm trying to Create an Indexed view however I'm getting the following error

Cannot create index on view '' because the view uses an implicit conversion from string to datetime or smalldatetime. Use an explicit CONVERT with a deterministic style value.

The issue is with an INT column [GPSTime] that records the number of seconds from '1970-01-01 00:00:00' and I'm trying to CONVERT/CAST this is to a DATETIME, eg

CAST(DATEADD(SS,[GPSTime],'1970-01-01' ) AS DATETIME)

or

CONVERT(VARCHAR,DATEADD(SS,[GPSTime],'1970-01-01' ),113)

or

CONVERT(DATETIME,DATEADD(SS,[GPSTime],'1970-01-01' ),113)

Each of the three options above gives me the error I mentioned earlier.

Is the way around this?

Answer 1

Going to make a guess that the issue is actually on the '1970-01-01', try this:

Dateadd(ss, gpsTime, convert(datetime, '1970-01-01', 101))

Or you could keep the datetime value in another table (as a datetime to avoid convert) or write a deterministic function to return your datetime as such:

create function [dbo].[UnixEpoch]
()
returns datetime
with schemabinding
as
begin
    RETURN convert(datetime, '1970-01-01', 101)
end

go

select objectproperty(object_id('[dbo].[UnixEpoch]'), 'IsDeterministic')

SELECT dbo.unixEpoch()

EDIT:

note the datetime style applied to the convert(datetime, '1970-01-01', 101)

according to documentation at http://msdn.microsoft.com/en-us/library/ms178091.aspx :

Source or target type is datetime or smalldatetime, the other source or target type is a character string, and a nondeterministic style is specified. To be deterministic, the style parameter must be a constant. Additionally, styles less than or equal to 100 are nondeterministic, except for styles 20 and 21. Styles greater than 100 are deterministic, except for styles 106, 107, 109 and 113.

Answer 2

i got the same error and fixed it by not only using convert:

CONVERT(DATETIME, firstdate, 102) AS firstdate

On the SELECT clause, but also in the WHERE clause as:

WHERE  firstdate >= CONVERT(DATETIME,'20150101',102)

I hope this solution helps!