Date/Time Format change in system setting affects Data Export

[Steve 3T]

I was recently going through our email templates and noticed that the dates and times were in the wrong format.

I went into the advanced settings and found that the system.regionalSettings.dateFormat wasn't set to a British format. I changed it to dd/MM/yyyy. Checking the emails being sent, the dates were now looking good.

At the same time, our Hornbill Data Extract tool stopped working (I didn't know it at the time). This is because our SQL database was now rejecting the date/times which were being imported.

Using the direct database query, I can now see that there are a mixture of date and time formats within the hornbill database.

Why does changing the system.regionalSettings.dateFormat actually change how the date is saved within the database? Please can anyone indicate a way to fix this? Our reporting is now completely broken.

In addition to this, I need to find a way of having the British date format in emails, but saving the date in the correct format in the database so the extract tool can work correctly (or does the extract tool need fixing?)

[James Ainsworth]

7 hours ago, steve.gibson@3t-europe.com said:

I went into the advanced settings and found that the system.regionalSettings.dateFormat wasn't set to a British format. I changed it to dd/MM/yyyy. Checking the emails being sent, the dates were now looking good.

Good to hear that this has helped.

 

7 hours ago, steve.gibson@3t-europe.com said:

Using the direct database query, I can now see that there are a mixture of date and time formats within the hornbill database.

If a field is a specific date/time field then these should stick to a particular format.  This follows a UTC format.  The places where you see a different formation, I would start by confirming if the field is a date/time field or a string field that contains a date.  You can use the Entity View to see the different field types.

 

8 hours ago, steve.gibson@3t-europe.com said:

At the same time, our Hornbill Data Extract tool stopped working (I didn't know it at the time). This is because our SQL database was now rejecting the date/times which were being imported.

I can't say for sure, but this might be down to the field type that is being used and the format of the date that is being imported.  As above, a datetime field will be expecting a particular format.

 

8 hours ago, steve.gibson@3t-europe.com said:

Why does changing the system.regionalSettings.dateFormat actually change how the date is saved within the database? Please can anyone indicate a way to fix this? Our reporting is now completely broken.

I don't believe that this does change how dates are saved.  This only changes how dates are displayed.  In most cases the displaying of a date to a user in the UI will apply the date format defined in their profile, allowing each user to have their preferred format.  There are some automated actions that are done without having context of a user and therefore can't use the defined format from a user's profile.  For example, an automated email can be sent without having the context of a user to apply their datetime profile settings, so the regional settings are used as a default for these scenarios. 

 

I think the starting point is to determine if the places where dates are being used and they appear to be causing problems are datetime fields or string fields (varchar, longext).

[Steve 3T]

Thanks for getting back to me.

You are correct, it looks like the data in the database is still in the original format. However, I think there is a bug somewhere.

If you see the screenshots attached, if I preview the report that the extractor tool uses, you can see that the dates are in the format stored in the database. However, the report file which is downloaded as the extractor tool runs, is actually in the format set by the regional settings.

I then looked at the report itself and saw that you could actually select "Raw" under each column. I assumed that meant it would then output that column without any formatting, but unfortunately, it does not.

Any ideas please?

 

[Steve Giller]

If that second screenshot is, as it appears, from Excel it's likely that any changes to the date format are being done in Excel itself.
If you export it as a CSV and look at the raw text file you will see exactly how it's being exported.

[Steve 3T]

Thanks for the response.

Looking in the CSV, the date format is the same as the regional settings. I beleive this is why the extract tool is now broken because the date format in the report has changed.

[James Ainsworth]

Hi Steve,

I've mentioned what you are experiencing with the RAW option not providing the raw value to our development team.  I've also replicated it.  If an issue is identified.   I'll post back here if I have any updates.  

[Steve 3T]

Thanks James, do we have an estimated time?

I think I will have to revert the regional settings if it's not something that can be looked into quickly. We currently have no external reporting available to the senior management.

[James Ainsworth]

Hi Steve,

Nothing yet to update you with.  Once an investigation is done I'll let you know.

[James Ainsworth]

HI Steve,

The root cause for the Raw option within the reporting not providing an unformatted date has been identified, but as the date formatting can affect so many areas, it is going to require some time to ensure the change is done without impacting other areas.  So, for now, you may want to revert that setting to the default format.  If I get any news on timeframes I'll let you know.

[Steve 3T]

Hi,

Is there any update on this please?

Thanks!

[Martyn Houghton]

@James Ainsworth

Is there a Problem Reference number for this defect and is there an update on the plan to deploy the fixes for this?

Cheers

Martyn

[James Ainsworth]

Hi Martyn,

If this is an issue that you are also experiencing, I would recommend raising this with Hornbill Support.  

Many thanks,

James

[James Ainsworth]

I'm pleased to let you know that a fix has been put in place and it will be available in build 3479 of the Platform.  Please keep an eye open for the release notes.

Regards,

James