I have the following problem:
I imported a csv file in MC and there is a date field in it. When I create the billing_date attribute as text field, all of the csv data will be imported correctly and the date will have the dd.mm.yyyy format. When I create the attribute as date field, around 2/3 of the data gets lost. In my entry data extension is the RechnungsDatum field build in as string as well.
So here is my SQL:
SELECT CONVERT (NVARCHAR, import.billing_date, 104) as RechnungsDatum . . . AND CAST(import.billing_date AS DATE) BETWEEN DATEADD (dd, -1, CAST(GETUTCDATE() AS DATE)) AND DATEADD (dd, 0, CAST(GETUTCDATE() AS DATE))
I get the following error:
Could not convert date and/or time from string data type.
Do you have an idea how exactly should i convert string to date so that my query would give a result?