I'm experimenting with using a string for storing different kind of data types in a database. When I do queries I need to cast the strings to the right type in the query itself. I'm using .Net with NHibernate, and was glad to learn that there exists functionality for this.

Consider the simple class:

public class Foo
{
    public string Text { get; set; }
}

I successfully use Projections.Cast to cast to numeric values, e.g. the following query correctly returns all Foos with an interger stored as int - between 1-10.

var result = Session.CreateCriteria<Foo>()
    .Add(Restrictions.Between(Projections.Cast(NHibernateUtil.Int32, Projections.Property("Text")), 1, 10))
    .List<Foo>();

Now if I try using this for DateTime I'm not able to make it work no matter what I try. Why?!

var date = new DateTime(2010, 5, 21, 11, 30, 00);
AddFooToDb(new Foo { Text = date.ToString() } ); // Will add it to the database...

var result = Session
    .CreateCriteria<Foo>()
    .Add(Restrictions.Eq(Projections.Cast(NHibernateUtil.DateTime, Projections.Property("Text")), date))
    .List<Foo>();