CODEWITHSUNDEEP
t-sqlsql-server-2005sql-server-2008
M.Ford
M.Ford

Reputation: 539

TSQL date comparison returns erroneus text centric error

Right then, I have another one. This is the same proc as I was working with before. The results weren't what I needed so went back to the drawing board. The system is the same:

SQL Server 2005 || Excel 2007 || SQL Management studio 2008R2

I am having a problem comparing two dates. The error that SSMS is throwing makes no sense. Here is the code:

declare @calendar_start char(8)
declare @years int

set @calendar_start = '20130101'
set @years = 1

----------------------------------------------------------------------------
-- standard declaration stuff.  -
----------------------------------------------------------------------------

-- for ease of entry, I convert the start timeto a real time ---------------
----------------------------------------------------------------------------
declare @startdate datetime
set     @startdate = CONVERT (datetime, @calendar_start, 112)

-- to calculate the end of the forecast, I use the start date and add the --
-- provided number of year -------------------------------------------------
----------------------------------------------------------------------------
declare @enddate datetime
set     @enddate = dateadd(year,+@years,@startdate)
----------------------------------------------------------------------------

-- I need a variable to advance the plotting month. the plotting month is --
-- how I am going to spread out the project all year from a single date ----
----------------------------------------------------------------------------
declare @counter int
set     @counter = 0
----------------------------------------------------------------------------

----------------------------------------------------------------------------
-- this table will be used to have all the calendar dates by year-month ----
----------------------------------------------------------------------------
create table #calendar (calenderid char(6))

insert into #calendar
    select 
        distinct left(calendarid,6) [yearmonth]
    from 
        [cmdb_core].[dbo].[Calendar] 
    where 
        datevalue between @startdate and @enddate
----------------------------------------------------------------------------

----------------------------------------------------------------------------
-- rather than hitting the database any number of times, I load the whole --
-- of the computed estimates table into memory. it is faster that way.  ----
----------------------------------------------------------------------------
create table #baseline (
    [adjusted_ExpectedActionDt] datetime
    ,[key] text
    ,projectid text
    ,projectnm text
    ,ParentChaseProjectNo text
    ,VersionTag text
    ,itemid text
    ,Qty int
    ,ItemNotes text
    ,CashflowType text
    ,frequency text
    ,UnitPrice float
    ,[cost] float
)

insert into #baseline (
    [adjusted_ExpectedActionDt]
    ,[key]
    ,projectid
    ,projectnm
    ,ParentChaseProjectNo
    ,VersionTag
    ,itemid
    ,Qty
    ,ItemNotes
    ,CashflowType
    ,frequency
    ,UnitPrice
    ,[cost]
)
select  
    case
        when (ExpectedActionDt is not null)
            then ExpectedActionDt
        when (IntegratedReleasePlanDt is not null)
            then IntegratedReleasePlanDt
        else
            DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)
    end [adjusted_ExpectedActionDt]
    ,cast(ModelEstimateId as nvarchar(max))+cast(BucketId as nvarchar(max))+cast(ItemNo as nvarchar(max)) [key]
    ,projectid
    ,projectnm
    ,ParentChaseProjectNo   
    ,VersionTag
    ,itemid
    ,Qty
    ,ItemNotes
    ,CashflowType
    ,frequency
    ,UnitPrice
    ,null [cost]
from 
    estimate.ComputedEstimates
where
        [status] <> 'Hold'
    and CostCategory <> 'Assembly'
    and includeinforecast = 'Y'
    and cashflowtype <> 'Notional'
    and Qty <> 0
    and case
            when (ExpectedActionDt is not null)
                then ExpectedActionDt
            when (IntegratedReleasePlanDt is not null)
                then IntegratedReleasePlanDt
            else
                DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)
        end between @startdate and @enddate
--------------------------------------------------------------------------

--------------------------------------------------------------------------
-- we need a place to contain the results of the interation through the --
-- baseline temp table. the results table will be that. ------------------
--------------------------------------------------------------------------
create table #results (
    [adjusted_ExpectedActionDt] datetime
    ,[plot_date] datetime
    ,[key] text
    ,projectid text
    ,projectnm text
    ,ParentChaseProjectNo text
    ,VersionTag text
    ,itemid text
    ,Qty int
    ,ItemNotes text
    ,CashflowType text
    ,frequency text
    ,UnitPrice float
    ,[cost] float
)

-- this loop is how we will build the results. it is governed by the -----
-- date. as I iterate through the loop, I incriment the plot date so -----
-- that I can show a project and it's costs over the range of dates ------
-- rather than only the month it goes into production --------------------
--------------------------------------------------------------------------
WHILE (@startdate <= @enddate)
BEGIN
insert into #results (
    [adjusted_ExpectedActionDt]
    ,[plot_date]
    ,[key]
    ,projectid
    ,projectnm
    ,ParentChaseProjectNo
    ,VersionTag
    ,itemid
    ,Qty
    ,ItemNotes
    ,CashflowType
    ,frequency
    ,UnitPrice
    ,[cost]
)
select 
    [adjusted_ExpectedActionDt]
    ,dateadd(month,+@counter,[adjusted_ExpectedActionDt])
    ,[key]
    ,projectid
    ,projectnm
    ,ParentChaseProjectNo
    ,VersionTag
    ,itemid
    ,Qty
    ,ItemNotes
    ,CashflowType
    ,frequency
    ,UnitPrice
    ,case
        when frequency = 'OneTime' 
            then 
--=====================================================================--
--===================== this is where the problem is ==================--
--=====================================================================--           
                case
                    when dateadd(mm, datediff(mm,0, [adjusted_ExpectedActionDt]), 0) = 
                         dateadd(mm, datediff(mm,0, dateadd(month,+@counter,[adjusted_ExpectedActionDt])), 0)
                        then [Qty]
                    else
                        0
                end
--=====================================================================--
--=====================================================================--
        else
            cast(round((UnitPrice*Qty)/12,0) as int)
        end [cost]
 from #baseline

set @counter = @counter+1
set @startdate = dateadd(month,+1,@startdate)
END
--------------------------------------------------------------------------

--------------------------------------------------------------------------
-- now we have to return the results but it is not enough to just dump ---
-- the table, I have to add a date that covers all month regardless of ---
-- that month's presence in the results table. I use the calendar temp, --
-- which has those dates as a basis of the outer join --------------------
--------------------------------------------------------------------------

select  
    c.calenderid
    ,r.[adjusted_ExpectedActionDt]
    ,r.[plot_date]
    ,r.[key]
    ,r.projectid
    ,r.projectnm
    ,r.ParentChaseProjectNo
    ,r.VersionTag
    ,r.itemid
    ,r.Qty
    ,r.ItemNotes
    ,r.CashflowType
    ,r.frequency
    ,r.UnitPrice
    ,r.[cost] 
from 
    #calendar c
    left outer join
    #results r
    on c.calenderid = cast(year(r.[adjusted_ExpectedActionDt])as char(4))+RIGHT('0'+ CONVERT(VARCHAR,month(r.[adjusted_ExpectedActionDt])),2)
--------------------------------------------------------------------------
GO

The problem is:

--=====================================================================--
--===================== this is where the problem is ==================--
--=====================================================================--           
                case
                    when dateadd(mm, datediff(mm,0, [adjusted_ExpectedActionDt]), 0) = 
                         dateadd(mm, datediff(mm,0, dateadd(month,+@counter,[adjusted_ExpectedActionDt])), 0)
                        then [Qty]
                    else
                        0
                end
--=====================================================================--
--=====================================================================--

When I try to run this code, I get the following error:

Msg 402, Level 16, State 1, Line 149
The data types text and varchar are incompatible in the equal to operator.

This is most vexxing as the comparison is a date match and not text. This I have tried: 1. lots of internet searching 2. changing the case statement to an if statement but couldn't get the syntax right. 3. changing the dates to datepart functions only pulling the year. we know that should return an int 4. changing the comparison to "1=1" which is clearly numbers and should always be true 5. tried converting to concatenated text (yyyymm) 6. talked to one of our SQL developers (which I am not by title but expect to be by the end of this project :) )

the really strange thing is that this general code works

select 
    dateadd(mm, datediff(mm,0, getdate()), 0)
    ,dateadd(mm, datediff(mm,0, getdate()+60), 0)
    ,case
        when dateadd(mm, datediff(mm,0, getdate()), 0) = 
             dateadd(mm, datediff(mm,0, getdate()+60), 0)
            then 'matches'
        else
            'different'
    end

The last bit of information is that if I comment the offending code leaving only the [Qty] field, it runs fine.

help me Obi-anyone, you're my only hope. Thank you in advance.

Upvotes: 1

Views: 144

Answers (1)

GilM
GilM

Reputation: 3761

I think the problem isn't that part of your CASE statement, I think it's:

when frequency = 'OneTime'

because the frequency column is of datatype text.

You might try:

  when cast(frequency as varchar(20)) = 'OneTime'

or something.

Upvotes: 1

Related Questions