0

What I have:

  • A source data extension with slightly over 14000 records
  • Two attributes in the DE:

    • GlobalID (Text 10)
    • BirthDay (Text 8, YEARMONTHDAY, no dashes)
  • The target data extension has

    • GlobalID (Text 10)
    • Birthday (date)

I have encountered a super strange case while processing the data in the query studio and in automation studio. When I select TOP 10000 records:

SELECT TOP 10000
GlobalID,
CASE
  WHEN BirthDate IS NOT NULL THEN CONVERT(DATE, CONVERT(CHAR(8), BirthDate))
  ELSE NULL
END AS BirthDate
FROM Customer_Intermediate_2_1

The query studio in the marketing cloud will display the results, which is fine.

BUT! When I select TOP 12000 records:

SELECT TOP 12000
GlobalID,
CASE
  WHEN BirthDate IS NOT NULL THEN CONVERT(DATE, CONVERT(CHAR(8), BirthDate))
  ELSE NULL
END AS BirthDate
FROM Customer_Intermediate_2_1

The query studio in the marketing cloud fails and shows "Could not convert date and/or time from string data type".

This is really strange because I know for sure that the remaining records are valid. Is there any performance limitation on converting the data types in the marketing cloud? What could be the reason why it shows an error? Many thanks.

  • FYI: the lowest BirthDay value in the source DE is 19000101. – Juraj Resko Dec 10 '19 at 16:01
  • The total number of records you can process in SOQL is 10000 records according the the limits official doc. Could be Marketing Cloud also has that same limitation... – Brian Miller Dec 10 '19 at 18:53
  • 1
    Thanks @BrianMiller I also create a case to sf support to confirm the limit. Cheers, Juraj. – Juraj Resko Dec 11 '19 at 8:06
  • There is no technical limit in MC for SQL beyond running under 30 minutes :) – Doug Dec 13 '19 at 13:48

Your Answer

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

Browse other questions tagged or ask your own question.