For a short-term workaround on adding additional data types look at http://www.ormfoundation.org/forums/p/601/1835.aspx#1835. You could also combine this with custom properties (similar to http://www.ormfoundation.org/forums/p/573/1698.aspx#1698), which would give a cleaner user experience. In fact, you could probably go as far as a custom property that simply picks, and ignore the conceptual data type if it is populated.
The bigger question here is a conceptual model vs. physical model issue. From a per conceptual perspective, I can see datetimeoffset as being different from datetime, but it is hard to treat datetime and datetime2 as different types. What I would like to avoid is expanding the list of conceptual data types to be a superset of all of the implementations we target.
The current data types have served marginally for a long time, but they are basically just placeholders for real data types. Sometime this year I will be expanding the data types to allow simple type hierarchies (restrictions on other types) and formal facet definitions (currently, there are simply two slots that are shown and renamed at different times). This would allow, for example, a regex facet on a string data type, which isn't currently handled. It would also allow for extensions to add facets to core data types, including target-specific extensions like the SQL server data types. Basically, you could then add your own 'DataTimeOffset' data type to the model (with the SQL Server type specified as an extension facet) and use your own specialized type as the data type for multiple value types in your model.
Another parallel option is to add a settings file to the SQL generation. A settings file is a 'write once' generated file that the user can edit to affect the contents of other generated files. In the SQL case, you could then pick your target SQL server flavor and we could map the data types better for the newer SQL variants. This one is low hanging fruit (the NORMA generator already supports this concept).
So, while I'm reticent to simply extend the core list of data types, I need to address the broader issue soon.