We have several map services with layers (created from SQL Server tables/views) that display datetime fields. We also have HTML popups defined for many of them (table of visible fields). The default format for date fields appears to be "M/dd/yyyy h:mm:ss tt", but I would like to change it to "yyyy-MM-dd HH:mm:ss". Is there a way to define a default format on the server, so that it will apply to all date fields? Or is there a way to specify the format in the MXD using ArcMap? Things I have already tried or thought about:
- Changed the source query to reformat the fields using FORMAT([MyDatetimeField], N'yyyy-MM-dd HH:mm:ss'). That worked, but changed it to a text field, which had some unintended consequences in client applications, since they provide different options for text vs. date fields.
- Used the CONVERT function in the SQL query to reformat the field *and* keep it as a SQL datetime type. However, ArcGIS ended up reformatting it. Seems like as long as it's a date field, ArcGIS will change the format to its own default.
- Changed the datetime display settings on the Windows server using Control Panel. This changed the format within ArcMap but not in the published map service.
- I thought about formatting the HTML popup using XSLT, and using the format-dateTime function within that. However, it looks like only XSLT 1.0 is supported and the date/time format functions need 2.0.
- We could put code in the client applications to reformat datetime fields in the popups, but that defeats the purpose of defining the HTML popups on the server.
- While Googling, I did see some references to Field Configurations and the Production Mapping extension, but I currently don't have access to that.
It feel like there should be a simple way to do this, but I'm missing it.