Subsonic 3.0 ActiveRecord with dates

Question

I'm having a little trouble with a query.

In my database datetimes are stored as YYYY-MM-dd HH:mm:ss

I have the following LINQ query:

var visitors = Visitor.All().Where(x=>x.Date_Moderated < dateTime).OrderByDescending(x => x.Date_Moderated).Take(limit);

The problem is this is translated to:

SELECT TOP (100) [t0].VisitorId, <COLUMNS TRUNCATED FOR BREVITY AND PRIVACY>
FROM [dbo].[Visitor] AS t0
WHERE ([t0].[Date_Moderated] < '07/12/2010 18:53:58')
ORDER BY [t0].[Date_Moderated] DESC

As you can see the date parameter is not in the correct format and SQL Server converts this to US datetime and so I am getting no results when I should in fact get 100.

Does anyone know how to make Subsonic format the date correctly? Or alternatively a better way to structure my query.

Regards, Rob

score 1 · Accepted Answer · answered Dec 07 '10 at 14:40

I have solved this by changing my query to the following:

var myDb = new MyDB(); //Database context renamed for privacy
var select = myDb.Select.Top("100")
                        .From("Visitor")
                        .Where("Date_Moderated")
                        .IsLessThan(dateTime.ToString("yyyy-MM-dd HH:mm:ss"))
                        .OrderDesc("Date_Moderated");

var visitors = select.ExecuteTypedList<Visitor>();

Subsonic 3.0 ActiveRecord with dates

1 Answers1