none
Sql Error:Operand type clash: date is incompatible with int

    Question

  • Hi Everyone

    I'm gettign falowing error when trying to minus one day from date function.Below is my code and error.Please let me know where I'm doing wrong

    Error:operator type clash date is incompatible with int

    Code:

    	set nocount on
    	DECLARE @MigrationRunDate varchar(23)
    select @MigrationRunDate = WynsureCBLCrossReference.dbo.udf_getXrefDate('Migration Run Date')
    	
    Select (case
    		when ProductName like 'CCT%' then
    			convert(
    				varchar(23),
    				dateadd(day,(dateadd(
    					year,
    					((datepart(year, convert(date, @MigrationRunDate)) * 10000 + datepart(month, convert(date, @MigrationRunDate)) * 100 + datepart(day, convert(date, @MigrationRunDate))) -
    					(datepart(year, convert(date, Eff_Date)) * 10000 + datepart(month, convert(date, Eff_Date)) * 100 + datepart(day, convert(date, Eff_Date)))) / 10000 / 10 * 10 + 10,
    					convert(date, Eff_Date)))-01,convert(date, Eff_Date)),
    				101)
    		else
    			cast(NULL as varchar(23))
    	end) as RenewalOrEndOfTermDate						from
    	DataWarehouseStaging..stgPolicyFrozen 
    		left outer join WynsureCBLCrossReference..xrefCoverage xrefCoverage on
    	cast(ltrim(rtrim(xrefCoverage.PlanCodes)) as int) = cast(ltrim(rtrim(stgPolicyFrozen.Plan_Code)) as int) and
    	cast(ltrim(rtrim(xrefCoverage.CompanyNumber)) as int) = cast(ltrim(rtrim(stgPolicyFrozen.Company)) as int) 

    • Moved by Shulei Chen Monday, September 10, 2012 1:09 AM (From:Getting started with SQL Server)
    Thursday, September 06, 2012 5:35 PM

Answers

All replies

  • Hello,

    Check out the thread below. It may help you.

    http://social.msdn.microsoft.com/Forums/en/transactsql/thread/fd29f3b3-b1fb-4910-bf13-a7bf601cf4f7

    Friday, September 07, 2012 8:29 AM
  • Take a look at this blog post

    Operand type clash: date is incompatible with int error when trying to do +1 on a date data type in SQL Server 2008


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, September 10, 2012 3:03 AM
    Moderator
  • Use the dateadd() function.  Other useful ones include datepart() and datediff().

    BrainE

    Friday, November 08, 2013 4:20 PM
  • Your mindset is wrong. In SQL temporal is a data type; you are writing 1960's COBOL where dates were strings! 

    CONVERT() is a 1970's propriety Sybase SQL Server for string dates; good programmers use CAST() today. 

    Your “renewal_or_end_of_term_date” is an awful data element name. This is called “automobiles, squids and Lady Gaga” data element; it violates the Law of Identity from formal logic. It is many things stuffed like sausage in one value. This is not how to use SQL or to do RDBMS. 

    Your “udf_getXrefDate('Migration Run Date')” is another error. We do not use UDFs in good SQL. This is a declarative language! You are still writing procedural code. And these kludges do not optimize. 

    A column is a scalar, so the name “plan_codes” is a violation of ISO-11179 and basic data model. But worse than bad name is the fact that you have such crappy data kept in strings that you have to write things like this to fix it: 
     CAST(LTRIM(RTRIM(Xref_Coverage.plan_code)) AS INTEGER)
    Data scrubbing is done in another layer in a tiered architecture. It has its own tools. Etc. 
     
    And why do you think that cross references are part of a RDBMS? 

    You need more help than you can get on a forum. I would throw this whole schema and start over. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Monday, November 11, 2013 3:12 AM