I've found a possible bug with the Web Client (I've got a case open with Support but need to get a workaround in place whilst troubleshooting is ongoing). The issue is with dynamic date fields being looked up from SQL that are functioning correctly in the Full Client, but displaying the wrong date and generating invalid string errors in the Web Client. The server region is NZ, short date format is dd/MM/yyyy (for the Services as well as user accounts). The browser(s) language is English (NZ) - I've tested IE, Chrome, and Firefox - and the template fields format is Short Date (I've also tested with Custom Date set to dd/MM/yyyy).
In the Full Client, if the date in SQL is 2018-10-25, then a template with the date field can be assigned to a folder or document, the date field dynamically populates with "25/10/2018" when the lookup on the preceding fields runs, and the folder can be saved. If I try the same approach in the Web Client, the lookup runs, the date field is populated with "Thu, 25 Oct 2018, 00:00:00" but if I try to save it I get the error "Input string was not in a correct format".
I've tried creating folders via Workflow, assigning the template and populating the date field with a formatted date token (query SQL, format the returned date as dd/MM/yyyy, assign the field value) and the folder creates successfully, the Full Client shows the date correctly (e.g. as "25/10/2018") but the Web Client shows the wrong date of "2018/10/26 00:00:00" (i.e. adds a day to the date and yes, before you ask, the browser has the right DST setting). Further to that, if I use the same date token in the workflow in a folder rename activity, it displays correctly as "25/10/2018". And, the browser "Created date" and "Last modified" dates display in the correct, short date format in the Web Client, so dates work in Web Client as long as they aren't in template SQL lookup fields.
So, onto the workaround. Normal date fields not being looked up from SQL display correctly in the Web Client, so I'm trying to set up a SQL View for the template fields to look up, and have the date formatted as dd/MM/yyyy in the SQL view. However, the only function I can find is to "convert" the date field and it converts it to a non-date type no problem e.g. varchar, but not if I use "date" as the convert type e.g. "convert(date, datefield, 103)" ends up with 2018-10-25 in the SQL view, whereas "convert(varchar(20), datefield, 103)" gives me "25/10/2018", but then the template field errors with an invalid format error ... ! I need it to be a date field as it's for employee data so has to be a valid date field.
Any suggestions would be much appreciated.