title: SQLite won't match C# DateTime retrieved from database
body: <p>I'm attempting to run some unit tests on my application using SQLite in memory, but I've run into an odd problem:</p>
  
  <p>I have two queries.  The result of the first is the date of the most recent price list for a given list name, and that <code>DateTime</code> is used in the second query in order to fetch the most recent prices.  The problem is, the second query returns no results.</p>
  
  <p>Any idea what might be going wrong in the background here?</p>
  
  <pre><code>        var effective = DbSession.Current.CreateCriteria&lt;ItemPrice&gt;()
                  .SetProjection(Projections.Max("Effective"))
                  .Add(Restrictions.Le("Effective", workDate))
                  .CreateCriteria("PriceList")
                  .Add(Restrictions.Eq("ListName", listName))
                  .Add(Restrictions.Eq("Active", true))
                  .UniqueResult&lt;DateTime&gt;();
  
          return DbSession.Current.CreateCriteria&lt;ItemPrice&gt;()
              .Add(Restrictions.Eq("Effective", effective))
              .CreateCriteria("PriceList")
              .Add(Restrictions.Eq("ListName", listName))
              .Add(Restrictions.Eq("Active", true))
              .List&lt;ItemPrice&gt;();
  </code></pre>
  
  <p><strong>Result:</strong> Ended up implementing a custom IUserType to store the DateTime as a string, and adding a Fluent Automapping convention for <code>DateTime</code> to use it (included below):</p>
  
  <pre><code>class SQLiteDateTime : IUserType
      {
          #region IUserType Members
  
          public object Assemble(object cached, object owner)
          {
              return cached;
          }
  
          public object DeepCopy(object value)
          {
              var dt = (DateTime) value;
              return new DateTime(dt.Year, dt.Month, dt.Day, dt.Hour, dt.Minute, dt.Second);
          }
  
          public object Disassemble(object value)
          {
              return String.Format("{0:yyyy'-'MM'-'dd' 'HH':'mm':'ss.fff}", value);
          }
  
          public new bool Equals(object x, object y)
          {
              return x.Equals(y);
          }
  
          public int GetHashCode(object x)
          {
              return x.GetHashCode();
          }
  
          public bool IsMutable
          {
              get { return false; }
          }
  
          public object NullSafeGet(IDataReader rs, string[] names, object owner)
          { 
              string dateString = (string)NHibernateUtil.String.NullSafeGet(rs, names[0]);
              DateTime result = DateTime.ParseExact(dateString, "yyyy'-'MM'-'dd' 'HH':'mm':'ss.fff", CultureInfo.InvariantCulture.DateTimeFormat);
  
              return result;
          }
  
          public void NullSafeSet(IDbCommand cmd, object value, int index)
          {  
              if (value == null)
              {
                  NHibernateUtil.String.NullSafeSet(cmd, null, index);
                  return;
              }
              value = Disassemble(value);
              NHibernateUtil.String.NullSafeSet(cmd, value, index);
          }  
  
          public object Replace(object original, object target, object owner)
          {
              return original;
          }
  
          public Type ReturnedType
          {
              get { return typeof (DateTime); }
          }
  
          public NHibernate.SqlTypes.SqlType[] SqlTypes
          {
              get {   
              var types = new SqlType[1];  
              types[0] = new SqlType(DbType.String);  
              return types;  
              } 
          }
  
          #endregion
      }
  </code></pre>