IN-DB Error on the Data Stream Out (While connected to SQL Server)

[paultno]

Hello,

I am trying to do all my work IN-DB including any formulas.  I am receiving the following error:  

Data Stream Out (175) Error SQLExecute: [Microsoft][SQL Server Native Client 11.0][SQL Server]Conversion failed when converting date and/or time from character string.[Microsoft][SQL Server Native Client 11.0][SQL Server]Warning: Null value is eliminated by an aggregate or other SET operation.

This only happened after I created a variable for a formula tool. 

case
when "Trans_Date" < '2017-04-01'
then DATEADD(day,-366,'Trans_Date')
else DATEADD(day,-365,'Trans_Date')
end 

 I checked all the dates throughout the work flow and made sure they were all formatted as DATE.  Any idea why this error message appears in the Browse/Data Stream Out tools and not the formula tool itself?

Much appreciate,

Paul

[saubert · Accepted solution]

Hello, you should try without quote around field name (or at least not this kind of quote)

case
when Trans_Date< '2017-04-01'
then DATEADD(day,-366,Trans_Date)
else DATEADD(day,-365,Trans_Date)
end 

or maybe

case
when "Trans_Date" < '2017-04-01'
then DATEADD(day,-366,"Trans_Date")
else DATEADD(day,-365,"Trans_Date")
end 

[paultno]

That solved it!  

I can't thank you and this community enough!