0

I have a data extension in which date is importing as text. I want to convert this text to date for segmenting the data.

Here is my query, when i run this in automation I get 'Could not convert date and/or time from string data type.' error. However on validation it doesn't show any syntax error.

Select

CASE
WHEN [ACTIVATIONDATE] IS NOT NULL
THEN CAST(CONCAT(substring([ACTIVATIONDATE],7,10),
'/',substring([ACTIVATIONDATE],4,2),
'/',substring([ACTIVATIONDATE],1,2)) AS Date)
END


From DE1

Please help.

1
  • 3
    Could you share a few examples of [ACTIVATIONDATE]? Can it be that there's no value is null or empty in your DE? – Rafał Wolsztyniak Apr 1 at 12:30
0

I am guessing your dates are in the following format: DD-MM-YYYY and you are looking to output them as YYYY/MM/DD? If so, you should be able to use the CONVERT() function to do this and not need to do substring and concat functions.

Lets say the [date] value is '22-12-2020' (where this is December 22nd, 2020) and you want the date to be displayed as 2020-12-22.

By doing the following instead, you can greatly cut down the amount of code and processing required:

CONVERT(date,[date],103) as myDate

Which will output the date in the correct format for you.

Now the part that is likely tossing your error is if the date string is not in the correct format or is otherwise altered. What you might look at doing is something like:

SELECT CONVERT(date,[date],103) as myDate
FROM [myDE]
WHERE [date] IS NOT NULL
AND LEN([date]) = 10

This way it will only gather results where the date field is not null and that it matches the expected length of the format - which should remove the error you are getting.

Now there are a ton of further conditions you can put into place, for example: AND LEN(REPLACE([date],'-','')) = 8 which will verify it has the delimiters in it and the correct numbers. Or even AND ISNUMERIC(REPLACE([date],'-','')) = 1 to verify it is only numbers and not letters. But the level of scrutiny you go to on your conditions will depend on how 'trashy' your data is. (I do not mean this in a harsh way, just simply that the cleaner your data is, then the less conditions you need to account for to decrease risk of failure.)

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.