c# datetime value passing based on current date

I am trying to fetch records based on two dates from sql server... Select * from table where CreatedDate between @StartDate and @EndDate and i pass 5/12/2010 and 5/12/2010 (ie) fetching records for today... I have 17 records dated 5/12/2010 but none seems to get selected....

EDIT: I use this but when i debug my value it shows 5/12/2010 12:00:00AM

    DateTime baseDate = DateTime.Today;
    var today = baseDate;
    GetBookingReportByDate(today,today);

I am using these in c# and a resusable stored procedure which takes startdate and lastdate as parameters,

DateTime baseDate = DateTime.Today;

var today = baseDate;
var yesterday = baseDate.AddDays(-1);
var thisWeekStart = baseDate.AddDays(-(int)baseDate.DayOfWeek);
var thisWeekEnd = thisWeekStart.AddDays(7).AddSeconds(-1);
var lastWeekStart =开发者_JAVA技巧 thisWeekStart.AddDays(-7);
var lastWeekEnd = thisWeekStart.AddSeconds(-1);
var thisMonthStart = baseDate.AddDays(1 - baseDate.Day);
var thisMonthEnd = thisMonthStart.AddMonths(1).AddSeconds(-1);
var lastMonthStart = thisMonthStart.AddMonths(-1);
var lastMonthEnd = thisMonthStart.AddSeconds(-1);

I use these values and fetch records only based on startdate and lastdate... Exactly like stackoverflow Today,Yesterday,this week, last week,this month,Last month....

You didn't include the time portion...so both are getting parsed to the same value.

You need:

SELECT *
FROM Table
WHERE CreatedDate >= '5/12/2010 00:00:00'
    AND CreatedDate <= '5/12/200 23:59:59'

Or:

SELECT *
FROM Table
WHERE CreatedDate >= @StartDate
    AND CreatedDate <= DATEADD(day, 1, @StartDate)

UPDATE

After seeing your update, changing the query like my second example would still work. You could also make the change in your C# code:

GetBookingReportByDate(today, today.AddDays(1));

The problem is that SQL is comparing both the date and time. Thus you query translates to CreatedDate Between '2010-05-12 00:00:00.000' And '2010-05-12 00:00:00.000'. Instead, you should do something like:

CreatedDate >= @StartDate And CreatedDate < DateAdd(d,1,@EndDate).

Try this instead:

Select *
from table
where CreatedDate >= @StartDate
    and CreatedDate < @EndDate

and set @EndDate to "tomorrow"

If you don't want to change the sql statement, you can change the C# like this:

GetBookingReportByDate(today,today.AddDays(1));