sql server - SQL Issue querying database between two dates -

sql server - SQL Issue querying database between two dates -

i have next records in db below created date each record.

2013-11-09 12:55:43.000 2013-10-29 19:01:53.000 2013-10-29 04:59:42.000

my sql query looks

select d.name dealername, sum(c.commissionamount) commissionamount dealer d left bring together commission c on c.dealerid = d.dealerid c.createddate between isnull(@fromdate, c.createddate) , isnull(@todate, c.createddate) grouping d.name order commissionamount desc

when come in next dates in search functionality

from date = 29/10/2013 date = 09/11/2013

it returns 1 record, when should homecoming three, yet if leave date , pass in null date 2 records back

can tell me i'm doing wrong here?

thanks

try this:

when using dates in where clause utilize same casting on both sides

select d.name dealername, sum(c.commissionamount) commissionamount dealer d left bring together commission c on c.dealerid = d.dealerid cast(c.createddate date) between cast(isnull(@fromdate, c.createddate) date) , cast(isnull(@todate, c.createddate) date) grouping d.name order commissionamount desc

sql sql-server sql-server-2008