Jump to content

Date/Time Format change in system setting affects Data Export


Steve 3T
 Share

Recommended Posts

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?)

Link to comment
Share on other sites

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.

image.png

 

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).

Link to comment
Share on other sites

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?

 

Screenshot 2021-03-25 153208.png

Screenshot 2021-03-25 153257.png

Screenshot 2021-03-25 153449.png

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • 3 weeks later...
  • 4 weeks later...
  • DanielRi changed the title to Date/Time Format change in system setting affects Data Export

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...