1

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?

Kritner
  • 13,557
  • 10
  • 46
  • 72
Mazhar
  • 3,797
  • 1
  • 12
  • 29

2 Answers2

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.

Kritner
  • 13,557
  • 10
  • 46
  • 72
  • Thanks but with that change I'm getting "The view contains a convert that is imprecise or non-deterministic" – Mazhar Nov 19 '14 at 15:33
  • Changing that to `code` DATEADD(ss, GPSTime , CONVERT(DATETIME, '1970-01-01', 113)) still doesn't work – Mazhar Nov 19 '14 at 15:35
  • what about keeping the value as a datetime in a separate table that way you don't have to keep doing a convert per row? or writing a deterministic function that returns that datetime? – Kritner Nov 19 '14 at 15:37
  • a deterministic function that returns that datetime is good idea, Thanks – Mazhar Nov 19 '14 at 15:40
  • updated my answer, note that the first code snip of convert datetime i modified slightly, you need to provide a datetime style in the convert. – Kritner Nov 19 '14 at 15:49
0

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!

Daniel De Anda
  • 11
  • 1
  • 5