NHibernate and Ms Sql Server 2008: Date, Time, DateTime2 and DateTimeOffset

Ms Sql Server 2008 come with a lot of features talking about Date/Time. Besides the already known DbType.DateTime, now we have new types:

Date
Time
DateTime2
DateTimeOffset

NHibernate 2.1 introduce changes to support this types out-of-the-box. This table show all the details about how to configurate your system: classes, mappings:

CLR Type	DbType	Sql Server Type	NHibernate type
System.DateTime	DateTime	datetime	datetime
System.DateTime	DateTime2	datetime2	datetime2
System.DateTime	Date	date	date
System.TimeSpan	Time	time	TimeAsTimeSpan
System.DateTime	Time	time	time
System.DateTimeOffset	DateTimeOffset	datetimeoffset	datetimeoffset

When we talk about “NHibernate type”, we are refering to what you’ve to put into your mappings, i.e: <property name=”OnlineMeeting” type=”datetimeoffset”/> .

To use this features and more you’ve to use the dialect NHibernate.Dialect.MsSql2008Dialect.

The dialect changes include these types and changes to Hql functions that return the current system timestamp.

current_timestamp: Now returns the current system timestamp as a DateTime2 value. It uses sysdatetime Sql Server function at background.
current_timestamp_offset: New function!. Returns the current system timestamp with the offset as a DateTimeOffset value. It uses sysdatetimeoffset Sql Server function at background.

The idea of this post isn’t to explain every new type and what it can do, otherwise is to show you what have to do in your classes, queries or mappings to configure your date/time types with NHibernate. Besides, to know every detail of these types you can visit this useful .NET Framework Developer's Guide.

Posted Wed, 11 March 2009 02:44:00 PM by darioquintana

Filed under: SQL Server 2008, NH2.1, Date, DateTimeOffset, DateTime2, Time