Date filter problems in SqlServerSpatial 3.7

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Date filter problems in SqlServerSpatial 3.7

Bruno Scott
Hi all, since fdo 3.7 i can't filter on date.
I've digged into the debugger and find a strange behavior

The filter i'm trying to apply is
DATE_1 > ToDate('2007-01-02')

Internally, the provider send this to SqlServer
CONVERT(datetime2,SUBSTRING(N'2007-01-02',8,4)+'-'+
CASE UPPER(SUBSTRING(N'2007-01-02',4,3)) WHEN UPPER(LEFT(DATENAME(MONTH,'2000-01-01'),3))
THEN N'01' WHEN UPPER(LEFT(DATENAME(MONTH,'2000-02-01'),3))
THEN N'02' WHEN UPPER(LEFT(DATENAME(MONTH,'2000-03-01'),3))
THEN N'03' WHEN UPPER(LEFT(DATENAME(MONTH,'2000-04-01'),3))
THEN N'04' WHEN UPPER(LEFT(DATENAME(MONTH,'2000-05-01'),3))
THEN N'05' WHEN UPPER(LEFT(DATENAME(MONTH,'2000-06-01'),3))
THEN N'06' WHEN UPPER(LEFT(DATENAME(MONTH,'2000-07-01'),3))
THEN N'07' WHEN UPPER(LEFT(DATENAME(MONTH,'2000-08-01'),3))
THEN N'08' WHEN UPPER(LEFT(DATENAME(MONTH,'2000-09-01'),3))
THEN N'09' WHEN UPPER(LEFT(DATENAME(MONTH,'2000-10-01'),3))
THEN N'10' WHEN UPPER(LEFT(DATENAME(MONTH,'2000-11-01'),3))
THEN N'11' WHEN UPPER(LEFT(DATENAME(MONTH,'2000-12-01'),3))
THEN N'12' ELSE N'' END+'-'+SUBSTRING(N'2007-01-02',1,2)+
' 'SUBSTRING(N'2007-01-02',13,2)+':'+SUBSTRING(N'2007-01-02',16,2)+':'+SUBSTRING(N'2007-01-02',19,2)+':'+)


This filter was working fine with Fdo 3.4,3.5 and 3.6

Someone has an idea of what's going on?

Bruno Scott
Geomap
Reply | Threaded
Open this post in threaded view
|

Re: Date filter problems in SqlServerSpatial 3.7

Romica Dascalescu
Hi Bruno,

I'm not sure why before worked, but in fact it should not work.

Since your data value is not default FDO standard date time you need to specify the format.
E.g.: DATE_1 > ToDate('2007-01-02', 'YYYY-MM-DD')

Thanks,
Romy.

________________________________________
From: [hidden email] [[hidden email]] on behalf of Bruno Scott [[hidden email]]
Sent: Monday, November 19, 2012 10:30 AM
To: [hidden email]
Subject: [fdo-users] Date filter problems in SqlServerSpatial 3.7

Hi all, since fdo 3.7 i can't filter on date.
I've digged into the debugger and find a strange behavior

The filter i'm trying to apply is
*DATE_1 > ToDate('2007-01-02')*

Internally, the provider send this to SqlServer
/CONVERT(datetime2,SUBSTRING(N'2007-01-02',8,4)+'-'+
CASE UPPER(SUBSTRING(N'2007-01-02',4,3)) WHEN
UPPER(LEFT(DATENAME(MONTH,'2000-01-01'),3))
THEN N'01' WHEN UPPER(LEFT(DATENAME(MONTH,'2000-02-01'),3))
THEN N'02' WHEN UPPER(LEFT(DATENAME(MONTH,'2000-03-01'),3))
THEN N'03' WHEN UPPER(LEFT(DATENAME(MONTH,'2000-04-01'),3))
THEN N'04' WHEN UPPER(LEFT(DATENAME(MONTH,'2000-05-01'),3))
THEN N'05' WHEN UPPER(LEFT(DATENAME(MONTH,'2000-06-01'),3))
THEN N'06' WHEN UPPER(LEFT(DATENAME(MONTH,'2000-07-01'),3))
THEN N'07' WHEN UPPER(LEFT(DATENAME(MONTH,'2000-08-01'),3))
THEN N'08' WHEN UPPER(LEFT(DATENAME(MONTH,'2000-09-01'),3))
THEN N'09' WHEN UPPER(LEFT(DATENAME(MONTH,'2000-10-01'),3))
THEN N'10' WHEN UPPER(LEFT(DATENAME(MONTH,'2000-11-01'),3))
THEN N'11' WHEN UPPER(LEFT(DATENAME(MONTH,'2000-12-01'),3))
THEN N'12' ELSE N'' END+'-'+SUBSTRING(N'2007-01-02',1,2)+
'
'SUBSTRING(N'2007-01-02',13,2)+':'+SUBSTRING(N'2007-01-02',16,2)+':'+SUBSTRING(N'2007-01-02',19,2)+':'+)/

This filter was working fine with Fdo 3.4,3.5 and 3.6

Someone has an idea of what's going on?

Bruno Scott
Geomap



--
View this message in context: http://osgeo-org.1560.n6.nabble.com/Date-filter-problems-in-SqlServerSpatial-3-7-tp5017419.html
Sent from the FDO Users mailing list archive at Nabble.com.
_______________________________________________
fdo-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-users
_______________________________________________
fdo-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/fdo-users