Date formatting

[Ernst]

Under JIWA721...

Am having trouble with dates on SO_history Custom fields. Some dates are dd/mm/yyyy whole others are YYYY-MM-DDT00:00:00

Can I load something into my FormatCell to get all dates into dd/mm/yyyy layout.?

Public Sub FormatCell(ByVal BusinessLogicHost As JiwaApplication.IJiwaBusinessLogic, ByVal GridObject As JiwaApplication.Controls.JiwaGrid, ByVal FormObject As JiwaApplication.IJiwaForm, ByVal Col As Integer, ByVal Row As Integer, ByVal HostObject As JiwaApplication.IJiwaCustomFieldValues, ByVal CustomField As JiwaApplication.CustomFields.CustomField, ByVal CustomFieldValue As JiwaApplication.CustomFields.CustomFieldValue) Implements JiwaApplication.IJiwaCustomFieldPlugin.FormatCell


If CustomField.PluginCustomField.Name = "DateSent" Then
DirectCast(GridObject.ActiveSheet.Cells(Row, Col).CellType, FarPoint.Win.Spread.CellType.DateTimeCellType).Shortdateformat = "dd/MM/yyyy"
End If

[pricerc]

I'd parse them into dates and back out again.

.NET has DateTime.TryParse and TryParseExact; the latter IIRC allows you to specify a range of valid formats.

Of course the YYYY-MM-DDT00:00:00 format is the 'canonical' date format, and is probably a better format for storing in the database than the ambiguous dd/MM/yyyy... but that's a different topic.

[Ernst]

Ive used JiwaApplication.Manager.Instance.SysDateTime.ToString("yyyy-MM-dd") type format to get it into the correct format. By JIWA converts it back to YYYY-MM_DD, when saving, so that did help.

So ideally need the plugin to specify the correct format to save.

[Mike.Sheen]

Are you using SR1 or later of 07.02.01, Ernst?

Because we fixed some stuff in SR1 specifically related to custom fields and date formats - see DEV-7783

[Ernst]

Hi Mike,

We have the latest 7.02.01 with sr4 installed. Is their a way we can force the dd/mm/yyyy format via the plugin?

[Mike.Sheen]

The easiest way is to make sure your regional date format in Windows is set how you want it - because that is what we use out-of-the-box.

The values are stored in ISO 8601 format (yyyy-MM-ddTHH:mm:ss) - but that's not how it is displayed or captured.

I can spend some time looking into how you could customise the format - but if you have the Windows settings set right you shouldn't need this.

[Ernst]

We have the regional date setting in dd/mm/yyyy format, so it display on screen OK.

But we send the date on an EDI file, and the customers is getting confused as some dates are the one format, and some the the other.

So was hoping we could just store the date in dd/mm/yyyy format, as per prior JIWA versions, so all the dates would be stored the same.

Else we would need to adjust the Courier interface to also store in ISO format, and then adjust the EDI, to convert from ISO back to dd/mm/yyyy format for customer..

[Mike.Sheen]

So was hoping we could just store the date in dd/mm/yyyy format, as per prior JIWA versions, so all the dates would be stored the same.

Trust me - you do not want to do that. If dates are to be stored as text, you really need to use an unambiguous format like ISO 8601's yyyy-mm-dd format.

Else we would need to adjust the Courier interface to also store in ISO format, and then adjust the EDI, to convert from ISO back to dd/mm/yyyy format for customer..

That's going to be the best way to keep your dates future-proof. Anyone doing anything with a date stored as text will be able to deal with ISO 8601 date formats.