Subsonic 3.0 ActiveRecord with dates

Question

I have a slight problem with the request.

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:

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 it to a date and time in the USA, and therefore I do not get any results when I have to get 100.

Does anyone know how to properly set a date in Subsonic format? Or, alternatively, the best way to structure my query.

Regards Rob

Answer 1

I solved this by changing my request 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>();

Answer 2

?