Explicit conversion from data type datetime2 to int is not allowed.

Question

Post reply

Explicit conversion from data type datetime2 to int is not allowed.

@fracionero

Right there with Babe

Points: 737
More actions
November 26, 2011 at 7:14 am

#248554

I have an application that detects the datatype of a table column, in SQL 2000 it detects datetime, but in sql 2008 R2, detects datetime2(0), and when uses that to get a cast on the variable fails.
Does anyone any idea bout how to solve withoutto miodify the code?
Thanks
Francisco Racionero
twitter: @fracionero

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

okbangas SSChampion Points: 11773 More actions · Answer 1

Can you please post appropriate DDL and code?

Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

Concatenating Row Values in Transact-SQL[/url]

rfr.ferrari SSCertifiable Points: 6879 More actions · Answer 2

and post a sample of the data that you are considering!!

[font="Times New Roman"]rfr.ferrari[/font]
DBA - SQL Server 2008
MCITP | MCTS

remember is live or suffer twice!
the period you fastest growing is the most difficult period of your life!

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 3

@fracionero (11/26/2011)
I have an application that detects the datatype of a table column, in SQL 2000 it detects datetime, but in sql 2008 R2, detects datetime2(0), and when uses that to get a cast on the variable fails.
Does anyone any idea bout how to solve withoutto miodify the code?
Thanks

I am sorry your problem description & thread title is not matching 'Explicit conversion from data type datetime2 to int is not allowed.'

I am not sure what exactly the issue is. Per my understanding there should not be any issue with datetime conversion. There may be some issues if you try to convert Datetime to Int. Please post sample data / code as advised earlier.

@fracionero Right there with Babe Points: 737 More actions · Answer 4

I'm sorry but I was in a crisis and I haven't too much time to explain, and this issue is some particular.

Right now we have discovered the root cause of the issue.

The program in excution time query database engine to dicover the type of data in roder to create the query parameters for the query will be sent to SQL Server, in that moment with SNAC10 it discovers a string is a data type datetime2, maybe because the precision of string is microseconds, so SNAC10 allow this kind of data, asigns datatime 2 to that string and write the query:

sp_executesql N'select t.taskID from cases c join task t on c.caseid = t.caseid where c.PersonID = @P1 and t.Cancelled = 0 and

cast(t.DateTimeArrived as Int) > 0 and t.DepartmentID = @P2 and t.VisitTypeID = @P3 and t.TaskCaseDate between cast(@P4 as int) - @P5 and

cast(@P6 as int)

',N'@P1 int,@P2 int,@P3 int,@P4 datetime2(0),@P5 datetime2(0),@P6 datetime2(0)',198450,1363920,1379892,'2011-11-25 00:00:00','1900-01-29

00:00:00','2011-11-25 00:00:00'

but the query try to convert de paramter to int, and that conversion is not allowed.

The we have tried to use SNAC9, because this client doesn't recognice datetime2, and the runs well

exec sp_executesql N'select t.taskID from cases c join task t on c.caseid = t.caseid where c.PersonID = @P1 and t.Cancelled = 0 and

cast(t.DateTimeArrived as Int) > 0 and t.DepartmentID = @P2 and t.VisitTypeID = @P3 and t.TaskCaseDate between cast(@P4 as int) - @P5

and cast(@P6 as int)

',N'@P1 int,@P2 int,@P3 int,@P4 datetime,@P5 datetime,@P6 datetime',198450,1363920,1379892,'2011-11-25 00:00:00','1900-01-29

00:00:00','2011-11-25 00:00:00'

In this moment we are asking for development team to modify the way to know the datatype, asking to sys.columns to get the datatype_id, and then, get the real datatype of the coulm asking sys.types.

Other ideas will be well recived.

Francisco Racionero
twitter: @fracionero