Hello,

I hope you'll be able to help me: I need to quick fix a problem with timezone in a legacy application that use LLBLGEN 3.5 (adapter model) and SQL Server. So I need to change SQL column type from datetime to datetimeoffset (in several tables), but if I change that, the problem is that when regenerating the code llblgen also change the .NET type to DateTimeOffset, which cause compilation errors in the whole application.

So my question: Is it a way to generate code with DateTime instead of DateTimeOffset ? As DateTime minimally handle timezone (local/utc) , I suppose it should be possible ?

Thank you for your help

Can you change the .NET type of the Field, in the Entity Editor? If this shows an error, you can assign a TypeConverter in the Field mapping tab.

The .NET Type is always readonly and change when I change the db type, that means I can't change the mapping

In the type converter I only have DateTimeDateTimeUTCConverter. Do you know what it does ? I found this link but it's not clear: https://www.llblgen.com/Documentation/5.5/ReferenceManuals/LLBLGenProRTF/html/A1BC2ABB.htm

Thank you

A DateTimeOffset type in the database is returned by the SQL Server provider as a DateTimeOffset object so the .net type of the field changes. If you want to keep it a DateTime at the entity field level, you'll need a typeconverter. We don't ship such a type converter, but you can create one yourself rather easily, see: https://www.llblgen.com/Documentation/5.8/SDK/gui_implementingtypeconverter.htm. This is for 5.8 but 3.5 works the same in this case. Just be sure to reference the right assemblies and compile against .net 4.5.2

The type converter you have to create then has to convert between a DateTimeOffset and a DateTime value, using: https://docs.microsoft.com/en-us/dotnet/standard/datetime/converting-between-datetime-and-offset. The core type of the type converter should be a DateTime, which is the type the entity field will get.

Assign the type converter to all the field mappings of the entity fields mapped to the DateTimeOffset typed fields. They then should keep the DateTime type. This will cause information loss however, as DateTimeOffset of course contains other information. So if you're changing the field type to DateTimeOffset for a reason, you should look into fixing the application code to make sure the change to DateTimeOffset is used.

Thank you for the tips

Reason is quite simple: migrating database to SQL Azure. In this case database run in UTC timezone (you can change this but only for managed instance), when app server is another timezone. That cause issue when comparing date in specific queries. So I only need to convert between local (app server timezone) and utc (sql server timezone).

Then you don't need to convert to DateTimeOffset, but can keep using DateTime, and you need a typeconverter to/from UTC time, the one you linked to. So you should add the DateTimeDateTimeUTCConverter to the fields which need to convert from local time (entity) to UTC time (database) and back. It's shipped with v3.5: https://www.llblgen.com/Documentation/3.5/Designer/hh_goto.htm#Functionality%20Reference/SystemTypeConverters.htm#shipped

Is it possible to put it ON or OFF at runtime ? (based on a configuration) Because the database may or may not be on azure (and if not, it's a prerequisite to put on the same timezone than app server) Thank you

No, that's not possible per field. You can change the type converter tho (its source is available on the website in My account -> Downloads -> v3.5 -> Extras section) and e.g. read a config file entry there or use a static variable so you can set it from the outside and based on that variable you either do a conversion or simply pass through the value you get from the db / from the entity.

Ok perfect, I'll try this. Thank you for your quick help