Date and Time function not usable anymore

Question

Good day,

I use in Automation Studio, an SQL query using functions like dateAdd() and Datediff(). Since yesterday, this activity is errored with this message : " An error occured while checking the query syntax. Errors: The data types and time are incompatible in the add operator."

I didn't change this SQL activity since last october and it was running correctly almost every day.

Did I miss something in the releases?

here an example of my activity in SQL

Select 
dateadd(minute,dum.ID-1,cast(dateadd(day,dumday.ID-1,cast([Date Debut 
Campagne] as date)) as date) + cast([Heure Debut Campagne] + ':' + [Minute 
Debut Campagne] as time)) as currentDate,
dateAdd(minute,dum.ID-1,cast([Heure Debut Campagne] + ':' + [Minute Debut 
Campagne] as time)) as [CalcHour],
[Code Campagne SMS] as CodeCampagneSMS,
case [Concatenee] when 1 then [Nombre SMS - Minute] *2 else [Nombre SMS - 
Minute]  end as NBSMSMinute,Frequence
from [Campagne] as B1

Answer 1

So, I'm posting the results of a conversation with one of our DBA's this evening. He doesn't understand how it ever worked. Instead, he suggests you write it like this:

SELECT dateadd(minute,dum.ID - 1, dateadd(day, dumday.ID - 1, 
     dateadd(day, datediff(d, 0, [Date Debut Campagne]), cast([Heure Debut Campagne] + ':' + [Minute Debut Campagne] + ':00.000' as datetime))
   )) as currentDate,
   dateAdd(minute,dum.ID - 1,cast([Heure Debut Campagne] + ':' + [Minute Debut Campagne] as time)) as [CalcHour],
   [Code Campagne SMS] as CodeCampagneSMS,
   case [Concatenee] when 1 then [Nombre SMS - Minute] * 2 
                     else [Nombre SMS - Minute]  
    end as NBSMSMinute, Frequence
 from [Campagne] as B1

His notes::

datediff(d, 0, [Date Debut Campagne]) calculates the number of days from 'day zero'

cast([Heure Debut Campagne] + ':' + [Minute Debut Campagne] + ':00.000' as datetime) first builds a string in the time format "00:00:00.000', and then casts the to a datetime without specifying the date, so the date is set to 'day zero'.

The outer dateadd adds the number of days from 'day zero' the recorded event occurred to the 'day zero' time, and produces a datetime value built from the separately-recorded day, hour, and minute values.

Note: I didn't see your SQL so I cannot comment. But I hope this helps.