You are viewing limited content. For full access, please sign in.

Question

Question

Any suggestions on converting a SQL date field to dd/MM/yyyy format for dynamic template fields?

asked on September 13, 2018

Hi guys,

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.

Thanks,

Mike

1 0

Replies

replied on January 24

Hi Jon,

I've just checked and it's just been released in 10.4 (fix for bug 96323 as listed in the list of changes):

https://support.laserfiche.com/kb/1014025/list-of-changes-for-laserfiche-10-4

Regards,

Mike

1 0
replied on January 24

@Laserfiche any updates to this?

 

Thank you,

 

Jon

0 0
replied on January 24

Hi Mike,

 

I guess I could've checked there too! =)

 

Cheers!

 

Jon

0 0
replied on January 24

No worries Jon, I had raised a support case and worked through it until a hotfix was released at the end of the year but didn't think to check on the current status and post it in here, so thanks for the reminder.  :o)

0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.