datetime and timespan arithmetic in TSQL via HQL

Question

I need to create an HQL where clause which has the form:

where tbl1.DateTimeField + tbl2.TimeSpanField >= :someDateTimeParameter

The DateTimeField is of type DateTime

The TimeSpanField is of type BigInt (is this the best option?)

The someDateTimeParameter is a DateTime

writing the HQL query as above translates almost exactly into TSQL, however this is obviously invalid.

1. What do I need to do to get this where clause to work? Is there some HQL trick I can emlpoy?
2. If not, how do I write the HQL to use some TSQL function to do the comparison for me?
3. What TSQL would I need?

Thanks!

Andrew

Answer 1

For 1 and 2 see http://stackoverflow.com/questions/639522/performing-date-time-math-in-hql for answers. For 3 use dateadd or datediff (google for t-sql + function name)

Answer 2

I ended up storing the timespans as datetimes offset from 1/1/1900 like this

protected readonly DateTime leadTimeAdjustment = new DateTime(1900, 1, 1);

[Obsolete("For use by NHibernate only.")]
public virtual DateTime _leadTime { get; protected set; }

public virtual TimeSpan LeadTime
{
    get { return _leadTime - leadTimeAdjustment; }
    set { _leadTime = leadTimeAdjustment.Add(value); }
}