Fluent NHibernate Mapping and Formulas/DatePart

Question

Hi There,

i have a very simple table with a Datetime column and i have this mapping in my domain object.

MyDate is the name of the datetime column in the DB.

public virtual int Day { get; set; }
public virtual int Month { get; set; }
public virtual int Year { get; set; }
public virtual int Hour { get; set; }
public virtual int Minutes { get; set; }
public virtual int Seconds { get;set; }
public virtual int WeekNo { get; set; }

Map(x => x.Day).Formula("DATEPART(day, Datetime)");
Map(x => x.Month).Formula("DATEPART(month, Datetime)");
Map(x => x.Year).Formula("DATEPART(year, Datetime)");
Map(x => x.Hour).Formula("DATEPART(hour, Datetime)");
Map(x => x.Minutes).Formula("DATEPART(minute, Datetime)");
Map(x => x.Seconds).Formula("DATEPART(second, Datetime)");
Map(x => x.WeekNo).Formula("DATEPART(week, Datetime)");

This is working all great .... but Week Datepart.

I saw with NHProf the sql generating for a select and here's the problem it's generating all the sql correctly but for week datepart.. this is part of the SQL generated:

....Datepart(day, MyDate) ... ....Datepart(month, MyDate) ... ....Datepart(year, MyDate) ... ....Datepart(hour, MyDate) ... ....Datepart(minute, MyDate) ... ....Datepart(second, MyDate) ... ....Datepart(this_.week, MyDate) ...

where this_ is the alias for the table that nhibernate uses.

so it's treating the week keyword for the datepart stuff as a column or something like that. To clarify there's no column or properties that is called week.

some help ?

cheers

Alessandro

Answer 1

You dont specify the DB you use so im assuming SQL Server. Try the following

Map(x => x.WeekNo).Formula("DATEPART(wk, Datetime)");

or

Map(x => x.WeekNo).Formula("DATEPART(ww, Datetime)");

These are accepted abbreviations to WEEK.
Check out more here: http://msdn.microsoft.com/en-us/library/aa258265(SQL.80).aspx