Date format changing when creating dataset from Salesforce

Question & Answer
, ,
1

Hi
The dates in Salesforce are being changed to different format when creating dataset in Quicksight:
as example:
salesforce date: Quicksight data set date:
11/29/21 2023/05-11T00

How do I go about fixing? In the dataset, or in the analysis?
thanks in advance for help!

2

@bryce - could try with a calculated field (either in the data set or the analysis) like this:
parseDate({your date field}, ‘MM/dd/yy’)

3

thanks @Jesse - parse date doesn’t work (error in creation). formatDate just changes it to 5/11/23 (when year should actually be 2021)

4

@bryce My fault, I was using the salesforce date instead of the quicksight one. In the data set editor page, when you click the column in the data preview for your date field, does it give you an option to edit the date format there? If so, what is it showing the ‘original’ dates to look like?

Can you provide some more sample rows of what the dates look like in salesforce nd what its getting converted to in QuickSight? Trying to see if there is a pattern we can identify so we can fix it with a calc.

Also, what if you try converting it to a string in the data prep screen? What to the results look like?

5

@Jesse Can’t edit the date format. Changing to “string” gives the same (wrong) dates. If I try to change any other string columns, I have the option to edit the date format. But when changing my creation date from string back to date, I do not have that option.

6

@bryce ok, can you chare some more examples of the original and the incorrect dates so we can look for a pattern to try to correct it with a calculated field?

7

@Jesse - thanks for your help… here is sample:

|Salesforce Date |Quicksight Date|

|11/19/21 |2023-05-11T00:00:00.000Z|
|7/18/22 |2023-06-07T00:00:00.000Z|
|5/17/22 |2023-05-05T00:00:00.000Z|
|6/27/22 |2024-03-06T00:00:00.000Z|
|3/3/22 |2022-03-03T00:00:00.000Z|
|5/1/22 |2022-01-05T00:00:00.000Z|
|3/20/22 |2023-08-03T00:00:00.000Z|
|4/28/22 |2024-04-04T00:00:00.000Z|
|12/31/21 |2023-07-12T00:00:00.000Z|

8

@bryce The month and date I can figure out - its taking the month and treating it as day, and its taking the day and converting it to a month (basically modulo 12 to grab the remainder). There is one exception which is Nov 19 - not sure what it’s doing with that one.

This calc is a monster but it seems to work on all rows except Nov 19 2021. With some more sample data I may be able to figure it out. First change the Date field to a String in the data prep screen. Then add this calc and replace ‘QuickSight Date’ with your date field.

parseDate(concat(substring({Quicksight Date}, 9, 2), '/', toString(parseInt(substring({Quicksight Date}, 6, 2))+(parseInt(left({Quicksight Date},4))-2022+parseInt(substring({Quicksight Date}, 9,1)))*12), '/', toString(2022-parseInt(substring({Quicksight Date}, 9,1)))), 'MM/dd/yyyy')

Nonetheless, in parallel I think we should open a support case on this because something is obviously not working in the date conversion. Do you have access to the AWS support portal to open up a case?

9

Hello, I also have this problem, how do I go about creating a support case because using the formula above, thank you @Jesse for that, it is a fantastic solution but it does not work with all the dates I have.

Edit: this problem started yesterday (Jul 19), on Monday (Jul 18) my data looked fine so I don’t know if this is caused due to a QS update or something

10

Same problem here. This started around 7/18. I have pulled a large report from Salesforce and compared it with a CSV export from Quciksite - the results (of 1000s of records) show that the create_date and close_dates I compared were almost all wrong. The exception is when the date field in salesforce was something like 11/11/2021, or 4/4/2000, or whatever - in every case where the date had the same month and year, Quicksight dates were correct, in every case where they were different, QS was wrong. My theory is that Quicksite internal to the SF connector (this is NOT SOMETHING that can be configured in the data source connector or in the data source itself… it is not a calculated field, there are no formulas) - at any rate it looks like QS is reading the date European style DD-MM-YYYY, but SF is sending MM-DD-YYYY (american style). I also have a support ticket open.

11

Agree - sounds like something changed recently. Im not aware of any changes to our connector so perhaps the SFDC API is sending dates in a different format now.

Here is the info on how to open a support case - this is going to be the best way to get this fixed:

Sorry we couldnt find a quicker resolution!

12

Hello, thank you for the information. I just want to add that this problem happens when my dataset is created from a Salesforce report, I created a dataset from a Salesforce Object and now the dates appear correctly even though I will need to recreate my whole analysis, hopefully, this helps as a short-term solution.

13

@bwilson That’s helpful info to have. Thanks for sharing. The engineering team is looking into it as we speak.

14

Hi Jesse, Is there any update on this? I think I am having the same issue as what is raised in the thread. Please let me know, otherwise I can raise another ticket on the same issue.
Thanks

15

Hi @Waqas - if you are still seeing this issue I would recommend opening a support ticket as well. Sorry for the trouble!

1 Like