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

[Seth G]

Hi,

Are you thinking of a new processing parameter for the layer? 

E.g.

PROCESSING 'DATE_FORMAT=YYYY-MM-DD hh:mm:ss'

Seth

--
web:http://geographika.co.uk
twitter: @geographika

On Mon, Jan 22, 2018, at 5:27 PM, Martin Icking wrote:
> 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
> _______________________________________________
> mapserver-dev mailing list
> mapserver-dev at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/mapserver-dev