CODEWITHSUNDEEP
sql-server
mpenrow
mpenrow

Reputation: 5683

SQL conversion error because of datetime2

I have a view in a SQL Server 2008 database that has a datetime2 field. I need to be able to query from that view via a linked SQL server 2005. When I run open my cursor and fetch the records I get a "Conversion failed when converting datetime from character string." error. How do I make the cursor convert the datetime2 appropriately?

Here is my query that is failing

DECLARE @time DateTime

DECLARE db_cursor CURSOR FOR
SELECT [DateTime] FROM [LINKEDSERVER].[DATABASENAME].[dbo].[ViewName] 
  WHERE Name = 'blah'

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @time

WHILE @@FETCH_STATUS = 0
BEGIN
  Print @time
  FETCH NEXT FROM db_cursor INTO @time
END

CLOSE db_cursor
DEALLOCATE db_cursor

Any ideas?

Upvotes: 1

Views: 1085

Answers (1)

Oleg Dok
Oleg Dok

Reputation: 21776

try to declare cursor in following way:

DECLARE db_cursor CURSOR FOR
SELECT cast([DateTime] as DateTime) FROM [LINKEDSERVER].[DATABASENAME].[dbo].[ViewName] 
  WHERE Name = 'blah'

BUT

if remote values from DATETIME2 field is outside the allowed to DateTime type, then try the following

DECLARE @time NVARCHAR(100)

DECLARE db_cursor CURSOR FOR
SELECT CAST([DateTime] as NVArCHAR(100)) FROM [LINKEDSERVER].[DATABASENAME].[dbo].[ViewName] 
  WHERE Name = 'blah'

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @time

WHILE @@FETCH_STATUS = 0
BEGIN
  Print @time
  FETCH NEXT FROM db_cursor INTO @time
END

CLOSE db_cursor
DEALLOCATE db_cursor

Upvotes: 1

Related Questions