c# datetime value passing based on current date...

Question

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 = 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....

Answer 1

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));

Answer 2

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).

Answer 3

Try this instead:

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

and set @EndDate to "tomorrow"

Answer 4

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

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