Subsonic 3.0 select Query using DateColumn

Question

Hi ,

While selecting records using Date-Field i am facing a problem ,

my SQL2005 View (ViewOrders) StarDate column Have

4/23/2010 12:00:00 AM 4/23/2010 12:00:00 AM 4/23/2010 12:00:00 AM 4/23/2010 12:00:00 AM 4/23/2010 1:07:00 PM

My Code using subsonic 3.0

AMDB ctx = new AMDB(); SqlQuery vwOrd = ctx.Select.From();
vwOrd = vwOrd.And("StartDate").IsGreaterThanOrEqualTo("04/22/2010");//From date vwOrd = vwOrd.And("StartDate").IsLessThanOrEqualTo("04/22/2010");// To Date List cat = vwOrd.ToList();

i can able to fetch only first four records, i can't able to fetch the final record which start date contains(4/23/2010 1:07:00 PM). I think the problem is in the time format..

How can i code in subsonic ,which compare only the date in the date-time column.

I Don't need date "Between" method in subsonic, since i can get single date parameter(From date alone).

how can i solve this problem.

Thnks in advance

Answer 1

As a general rule, getting datetime records within a date range is normally achieved by (psuedo code)

DateField >= startdate AND DateField < (EndDate + 1 day)

I'm slightly confused by your example where your date records are all 23-Apr-10, but you seem to be searching for those on 22-Apr-10?

Also, as a general bit of advice, do date comparisons using the format 'YYYYMMDD' - this will cope with different servers in different date formats.