Data Type conversion precedence problem

SQL Server >> Data Type conversion precedence problem

by Jacco Schalkwijk » Mon, 13 Sep 2004 23:39:49 GMT

Strictly speaking I think your question is not about data type precedence,
which has stayed the same from SQL 7.0 to SQL 2000, but about the rules for
implicit conversion. Those rules have changed between SQL 7.0 and 2000. In
SQL 2000 implicit conversion is always determined by data type precedence.
In SQL 7.0 there was an exception to that rule, namely if you compare a
column with a non-column value (literal, variable, function or expression).
In that case the datatype of the column would always take precedence over
the datatype that it was compared with.

You can check the behaviour by running the example below on both versions.
On SQL 7 you should get one row returned, because the literal 11 is
converted to a VARCHAR, but on SQL 2000 the column is converted to an int,
and no row is returned:

CREATE TABLE #t (a VARCHAR(11))

INSERT INTO #t(a) VALUES (1)
INSERT INTO #t(a) VALUES (2)

SELECT a FROM #t WHERE a > 11

--
Jacco Schalkwijk
SQL Server MVP

"Kumar" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...
>I heard that if you upgrade user database from SQL 7.0 to SQL 2000(latest
>SPs),
> there is some data type conversion precedence problem and the same query
> will produce different results on SQL 7.0 and SQL 2000.
>
> I am sorry, I don't have any specific examples. If someone had this
> problem,
> please advice me with some examples.
>
> Thanks.

Top

TG, Could be that there are son values that are not a date. select adm_date from [ASES_MI60_Source].[dbo].[CLAIMS_SOURCE_CARRIER] where cast(ltrim(rtrim(adm_date)) as varchar(8)) not like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' AMB "TG" wrote: > Hi Alejandro! > > If I use the following: > > insert into test_adm_date([adm_date]) > select > adm_date > from [ASES_MI60_Source].[dbo].[CLAIMS_SOURCE_CARRIER] > > I am getting the same error as described in my original question. > > Help please? > > > > Hi TRacy! > > I ran your suggestion and got the following result: > > 20/05/1031 > > Thanks! > > TG > > > > Alejandro Mesa wrote: > > TG, > > > > > adm_date varchar (8) in table claims_source_carrier > > > > It seems that the format of the value in this column is yyyymmdd. If this is > > the case, then SQL Server will convert it to datetime implicitly and you do > > not need to reformat it. > > > > Example: > > > > create table dbo.t1 ( > > c1 datetime > > ) > > go > > > > insert into dbo.t1 values('20060627') > > go > > > > select * from dbo.t1 > > go > > > > drop table dbo.t1 > > go > > > > > > AMB > > > > > > "TG" wrote: > > > > > hi! > > > > > > I am using the following query: > > > > > > insert into test_adm_date([adm_date]) > > > select > > > SUBSTRING(LTRIM(RTRIM(a.[adm_date])),5,2) + '/' + > > > RIGHT(LTRIM(RTRIM(a.[adm_date])),2) + '/' + > > > LEFT(LTRIM(RTRIM(a.[adm_date])),4) > > > from [ASES_MI60_Source].[dbo].[CLAIMS_SOURCE_CARRIER] a > > > > > > > > > adm_date varchar (8) in table claims_source_carrier > > > > > > > > > > > > adm_date datetime in table test_adm_date > > > > > > > > > I get the following error: > > > > > > > > > Server: Msg 242, Level 16, State 3, Line 1 > > > The conversion of a char data type to a datetime data type resulted in > > > an out-of-range datetime value. > > > The statement has been terminated. > > > > > > > > > any help would be greatly apprecite it. > > > > > > Thanks! > > > > > > TG > > > > > > > >

Hi all, I keep getting this error, and the problem is the Minutes column? Disallowed implicit conversion from data type varchar to data type money, table 'tempdb.dbo.##tmp, column 'Minutes'. Use the CONVERT function to run this query. I'm basically adding the Column names of a table to the first row before the data before exporting to Excel. Declare @table varchar(50) set @table = 'tempdb..##tmp select * from ( select top 100 percent Customer, Service, [Call Start], charge, Minutes from (select 1 seq, 'Customer' Customer, 'Service' Service , 'Call Start' [Call Start] , 'charge' charge, 'Minutes' Minutes, union all select 2 seq, Customer, Service, [Call Start], charge, Minutes from tempdb..##tmp ) x order by seq, [Call Start] ) y The "select into" before the data is put in the temp table ##tmp for the minutes column looks like this: CAST(table1.Dur AS numeric) / 60 AS Minutes thanks gv

Hi All, Trying to change datatype of a Column to Money from nvarchar ALTER TABLE TESTTABLE ALTER COLUMN TESTCOLUMN MONEY I get this message below? Server: Msg 260, Level 16, State 1, Line 1 Disallowed implicit conversion from data type nvarchar to data type money, table TESTTABLE, column TESTCOLUMN. Use the CONVERT function to run this query. Sample data in that table: 0.95 4.31 NULL NULL NULL NULL 0.95 4.31 NULL NULL NULL NULL 0.95 CREATE TABLE [TESTTABLE] ( ...... , ......, [PICC_LD_Only] [nvarchar] (10) NULL ) ON [PRIMARY] thanks gv

I'm new to working with numeric data that must be very accurate. We're converting data from MS Access though the original source data is still in Dbase IV and I can use that. A typical number looks like: 0.7854350 In Access I linked to the SQL Server 2005 database and wrote INSERT INTO blah blah SELECT FROM queries to import data from 1 or usually 2 tables. Because the new database in SQL Server will contain data from multiple original tables, I used insert queries for this. No matter what I do, such as running Access's convert functions like CDec or CDbl or using the Float or decimal data types, one system or another rounds these numbers to the closest integer. I am not a math whiz, even remotely, so I'm lost when it comes to this. What is the proper technique to move data with 7 decimal places from Access to SQL Server? thanks! Don

SQL Server, Data Type conversion precedence problem

SQL Server >> Data Type conversion precedence problem