How to work around SQLite lack of DateTime2 support?

[Chris F]

Hi,

I've used a DateTimeConvention to map all my DateTime properties as
datetime2 in SQL2008. But now my Project.Data tests are broken because
SQLite's dialect does not support DateTime2.

Is there a workaround for this?

Thanks
- Chris

[Chris F]

I tried modifying my DateTimeConvention using a build condition for
unit testing:

public void Apply(IProperty target)
{
#if UNIT_TEST
target.CustomSqlTypeIs("datetime2(0)");
#else
target.CustomTypeIs("DateTime2");
#endif
}

giving a UNIT_TEST build mapping like:
<property name="CreatedDate" type="DateTime">
<column name="CreatedDate" sql-type="datetime2(0)" />
</property>

SQLite likes that fine, but NH doesn't push to SQL2008 as datetime2
like you'd expect for the UNIT_TEST build, as tested using
"01/01/0001"! Back to the drawing board.

...

[Snahider]

I faced a similar problem with the substring function. I extended the
dialect for the database

public class CustomSQLiteDialect:SQLiteDialect
{
public CustomSQLiteDialect()
{
RegisterFunction("substr", new StandardSQLFunction
("substr", NHibernateUtil.String));
RegisterFunction("substring", new StandardSQLFunction
("substr", NHibernateUtil.String));
}
}

In the config file use the CustomDialect:
<property
name="dialect">Tests.Northwind.Data.Dialect.CustomSQLiteDialect,
Northwind.Tests</property>

Try to add something like:

RegisterColumnType(System.Data.DbType.DateTime2, "date");

Links about SqlDialect:
http://github.com/gwenn/sqlite-dialect/blob/73d07f0990ecfd9b9a869066b8f7b3e023af39ff/org/hibernate/dialect/SQLiteDialect.java
http://ayende.com/Blog/archive/2006/10/01/UsingSQLFunctionsInNHibernate.aspx

[Chris F]

Thanks a lot,

I'll try and give this a shot today. I wonder if I'll still have
issues due to the nature of datetime not having the same range as
datetime2!

- Chris

On Aug 22, 3:14 am, Snahider <snahi...@gmail.com> wrote:
> I faced a similar problem with the substring function. I extended the
> dialect for the database
>
>     public class CustomSQLiteDialect:SQLiteDialect
>     {
>         public CustomSQLiteDialect()
>         {
>             RegisterFunction("substr", new StandardSQLFunction
> ("substr", NHibernateUtil.String));
>             RegisterFunction("substring", new StandardSQLFunction
> ("substr", NHibernateUtil.String));
>         }
>     }
>
> In the config file use the CustomDialect:
> <property
> name="dialect">Tests.Northwind.Data.Dialect.CustomSQLiteDialect,
> Northwind.Tests</property>
>
> Try to add something like:
>
> RegisterColumnType(System.Data.DbType.DateTime2, "date");
>

> Links about SqlDialect:http://github.com/gwenn/sqlite-dialect/blob/73d07f0990ecfd9b9a869066b...http://ayende.com/Blog/archive/2006/10/01/UsingSQLFunctionsInNHiberna...