Search code examples
c#sqlsql-servernhibernatenhibernate-criteria

Using SQL CONVERT function through nHibernate Criterion

I have a sql view, in which the data type of every column is string, for use with the jquery datatables plugin.

One of the columns contains a uk format date dd/mm/yyyy.

This column needs to remain a string to work with the keystroke filtering of the plugin, however for sorting it needs to be treated as a date.

I am using nhibernate criteria to create the sql query and I want to generate the following order by clause, as this orders the dates correctly

order by CONVERT (datetime, DateOfBirth, 103)

However in Nhibernate.Criterion.Projections there is no Convert method. There is Cast, but I get the following error due to the fact that it is a UK date format:

The conversion of a varchar data type to a datetime data type 
resulted in an out-of-range value

I have tried also tried the following:

 criteria.AddOrder(
             Order.Desc(
     Projections.SqlFunction(
                              "CONVERT",
                               NHibernateUtil.DateTime, 
                               new IProjection[]
                               {
                                 Projections.Property(propNames[orderByColumn]), 
                                 Projections.Property("104")
                               }
                             )
                         )
                   );

But I get the following error message:

NHibernate.HibernateException: Current dialect 
       NHibernate.Dialect.MsSql2008Dialect doesn't support the function: CONVERT

Can the SQL convert function be used when using Nhibernate.Criterion ?

Solution

  • If the setting 104 is not essential, we can get a quick solution: use CAST instead of CONVERT. This SQL Function is built-in in the NHibernate dialects:

    Projections.Cast(NHibernateUtil.DateTime
               ,Projections.Property(propNames[orderByColumn]))

    If the setting 104 is important we can create our own Dialect, register the CONVERT function, and use it from then ... forever

    Here Andrew Whitaker nicely shows how to