[mapserver-dev] Issue with WFS requests to SQL server with datetime columns

[Martin Icking]

Let's say I have data stored in MS SQL Server, and there are DataTime columns
in the table. When the client tries to make a GetFeature request on the
server, I got this error on MapServer: 

msParseTime(): Regular expression error. Unrecognized date or time format
(Apr 25 2018 12:00AM). 


I look at the code in mapmssql2008.c, in function prepare_database(), there
is such a piece of code: 

#ifdef USE_ICONV 
      snprintf(buffer + strlen(buffer), sizeof(buffer) - strlen(buffer),
"convert(nvarchar(max), [%s]),", layer->items[t]); 
#else 
      snprintf(buffer + strlen(buffer), sizeof(buffer) - strlen(buffer),
"convert(varchar(max), [%s]),", layer->items[t]); 
#endif 

It uses CONVERT() function to convert the column value to strings. When it
handles datetime, the default format for MS SQL Server is: 
mon dd yyyy hh:miAM/PM  (e.g. Apr 25 2018 12:00AM) 

However, according to this following link: 
http://mapserver.org/ogc/wms_time.html

MapServer only supports these datatime formats: 
Time Patterns Examples 
YYYYMMDD 20041012 
YYYY-MM-DDTHH:MM:SSZ 2004-10-12T13:55:20Z 
YYYY-MM-DDTHH:MM:SS 2004-10-12T13:55:20 
YYYY-MM-DD HH:MM:SS 2004-10-12 13:55:20 
YYYY-MM-DDTHH:MM 2004-10-12T13:55 
YYYY-MM-DD HH:MM 2004-10-12 13:55 
YYYY-MM-DDTHH 2004-10-12T13 
YYYY-MM-DD HH 2004-10-12 13 
YYYY-MM-DD 2004-10-12 
YYYY-MM 2004-10 
YYYY 2004 
THH:MM:SSZ T13:55:20Z 
THH:MM:SS T13:55:20 

That's why it causes the problem. I would suggest adding a format parameter
when calling CONVERT() for DateTime data. For example, we can use 120 format
(yyyy-mm-dd hh:mi:ss). In function prepare_database(), we can probably add
this following code piece: 

if (layerinfo->itemtypes && (layerinfo->itemtypes[t] == SQL_TYPE_DATE ||
layerinfo->itemtypes[t] == SQL_TYPE_TIME || layerinfo->itemtypes[t] ==
SQL_TYPE_TIMESTAMP)) { 
#ifdef USE_ICONV 
          snprintf(buffer + strlen(buffer), bufLen - strlen(buffer),
"convert(nvarchar(max), [%s], 120),", layer->items[t]); 
#else 
          snprintf(buffer + strlen(buffer), bufLen - strlen(buffer),
"convert(varchar(max), [%s], 120),", layer->items[t]); 
#endif 
      } 





--
Sent from: http://osgeo-org.1560.x6.nabble.com/Mapserver-Dev-f4252683.html