Using BETWEEN syntax with Symfony 1.4.x and MS SQL Server 2008

I´m using Symfony 1.4.8 and MS SQL Server 2008.

I had a query that read:

$q = Doctrine_Query::create()
    ->select('m.mapdate, m.created_at')
    ->from('GISMap m')
    ->where('m.mapdate BETWEEN ? AND ?', array($start_date, $end_date))
    ->orderBy('m.mapdate DESC');

This worked perfectly in MySQL, but when I switched to MS SQL Server I got an error like this one:



500 | Internal Server Error | Doctrine_Connection_Mssql_Exception



SQLSTATE[HY000]: General error: 10007 Incorrect syntax near '?'. [10007] (severity 5) [SELECT [g].[id] AS [g__id], [g].[mapdate] AS [g__mapdate], [g].[created_at] AS [g__created_at] FROM [gis_map] [g] WHERE ([g].[mapdate] BETWEEN '2010-03-24 10:32:24' AND ?) ORDER BY [g].[mapdate] DESC]. Failing Query: "SELECT [g].[id] AS [g__id], [g].[mapdate] AS [g__mapdate], [g].[created_at] AS [g__created_at] FROM [gis_map] [g] WHERE ([g].[mapdate] BETWEEN '2010-03-24 10:32:24' AND ?) ORDER BY [g].[mapdate] DESC"



After doing some research I found out that the replacement of the ? for the actual values is done by the PDO driver. So I decided that the problem was the driver for SQL Server didn´t like the array being passed to it.



I change the syntax to:

$q = Doctrine_Query::create()
   ->select('m.mapdate, m.created_at')
   ->from('GISMap m')
   ->where('m.mapdate >= ?', $start_date)
   ->andWhere('m.mapdate <= ?', $end_date)
   ->orderBy('m.mapdate DESC');

Now it works. It´s annoying to have to do this, but it´s been my experience that working with SQL Server will get you into these troubles.