2

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
  • I will ask around internally and post something EOD. – Timothy Mar 20 '18 at 15:48
  • We're looking into this. Can you tell me some vague identifying info? Like what stack you're on? – Timothy Mar 20 '18 at 19:32
  • I have talked to a lot of people regarding this, and I have some ideas. But I need to talk to a few more, and will likely have something tomorrow (March 21 2018). – Timothy Mar 20 '18 at 20:11
  • I spoke with the Data team about what you can give us without posting any confidential/risky info. Here is what I would like from you: the name of your Data Extension. We can find all we need based on that. – Timothy Mar 20 '18 at 23:16
  • Hello Timothy, thank you very much for your answers. The name of my target Data extension is : Lissage_Temp_Calcul – Pierre-Arnaud Mar 21 '18 at 7:54
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.

  • Thank you very much for your help Timothy. I opened a case. I will work on changing my query. – Pierre-Arnaud Mar 22 '18 at 13:59
  • I think the above will fix it. The case will be helpful for me to figure out why things stopped working earlier this week. – Timothy Mar 22 '18 at 14:46
  • @Pierre-Arnaud, I'm still doing some research on this end t make sure anything else isn't gone wrong. If you could go into the original query, and do a "parse check", let me know the results. – Timothy Mar 23 '18 at 0:35
  • Yes I was able to modify my query and it runs correctly. The results are fine. Thank you very much for your time and help. – Pierre-Arnaud Mar 23 '18 at 17:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.