I am using LLBLGen v3.1 with an adapter project targeting the 4.0 framework and a SQL Server Express database. I have a table that has a column (SENT_DTM) defined as 'datetimeoffset'. The code that LLBLGen produces maps this field to a DateTime type in .NET. When I attempt to insert a row into this table with the SentDtm set to DateTime.Now the following SQL is produced:

Query: INSERT INTO [ABP].[dbo].[ALERT] ([INSERTED_DTM], [SENT_DTM], [SUBJECT], [TEXT], [TRANSIT_ACCOUNT_ID]) VALUES (@p2, @p3, @p4, @p5, @p6) ;SELECT @p1=SCOPE_IDENTITY()
Parameter: @p1 : Decimal. Length: 0. Precision: 18. Scale: 0. Direction: Output. Value: 18.
Parameter: @p2 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 12/15/2011 1:54:20 PM.
Parameter: @p3 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 12/15/2011 11:54:20 AM.
Parameter: @p4 : AnsiString. Length: 20. Precision: 0. Scale: 0. Direction: Input. Value: "test subject".
Parameter: @p5 : AnsiString. Length: 2000. Precision: 0. Scale: 0. Direction: Input. Value: "12/15/2011 13:54:20".
Parameter: @p6 : Decimal. Length: 0. Precision: 18. Scale: 0. Direction: Input. Value: 15.

When I look directly at the database row, the offset in the SENT_DTM column for the row I just inserted is +00:00. I would have expected it to be -05:00 since I am in the US Eastern Time zone. A little more fiddling with some direct SQL execution (outside LLBLGen) shows that I have to specify the zone offset in the SQL like this: 12/15/2011 1:54:20 PM -05:00. How can I get LLBLGen to generate the zone offset in its SQL statement?

For apps that use timezones it's recommended to store all in UTC, then convert the DB dates to the client timezone for display. That way you eliminate this problems.

Now back to the issue. I don't recall whether or not LLBLGen send timezone info, since the parameter is just put in the SqlCommand object which perform the translation to string. So please check that you are actually specifying the timezone info in the datetime value field. Maybe the timezone info for that variable is not set.

Copied from old server:

jlkInChantillyVA wrote:

Understood. But if I wanted to use the DatetimeOffset data type in the database is there a WAY to get LLBLGen to produce the offset (i.e. -05:00) in the SQL?

It should work. See this test:

var customer = new CustomerEntity();
customer.BirthDateWithOffset = DateTime.Now;
customer.BirthDate = DateTime.Now;

using (var adapter = new DataAccessAdapter())
{
    adapter.SaveEntity(customer);
}

Generated sql:

INSERT INTO [OffsetTest].[dbo].[Customer] ([BirthDate], [BirthDateWithOffset]) VALUES (@p1, @p2) ;
SELECT @p3=SCOPE_IDENTITY()
Parameter: @p1 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 12/19/2011 7:46:30 PM.
Parameter: @p2 : DateTimeOffset. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 12/19/2011 7:46:30 PM -06:00.
Parameter: @p3 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Output. Value: 1.

Looking into your generated SQL it's obvious that the problem is that the parameter for SENT_DTM is DateTime and not DateTimeOffset. DateTime per-se doesn't have timezone information.

I found this weird because you said the database field is datetimeoffset. If the database type is datetimeoffset then it's mapped to the entity field as System.DateTimeOffset, if you change it to DateTime in the Designer it will inform you about a validation error and you can't generate code if you have validation errors.

So, either you applied a TypeConverter on that field mapping or your datetimeoffset db column was mapped to DateTime, and if that is the case maybe you are using a very old LLBLGen version (I find that very unlikely because datetimeoffset was added since v2.6). So, please do this:

1. Make sure you are using the latest LLBLGen version.
2. Make sure your db type is datetimeoffset. Then refresh your model from the database.
3. Validate the model, then re-generate the code.

If you still have problems, please attach your .llblgenproj file. You can do that in a HelpDesk thread, which is private.

We did decide to go with GMT. However, I also figured out what the problem was. The only change to that table was one field from DateTime to DateTimeOffset. The designer did not pick up that difference and continued to generate .NET DateTime instead of DateTimeOffset. However, if I delete the table from the project in the designer and then readd it, the .NET type is changed to DateTimeOffset and all is well. Possible designer bug?

jlkInChantillyVA wrote:

However, I also figured out what the problem was. The only change to that table was one field from DateTime to DateTimeOffset. The designer did not pick up that difference and continued to generate .NET DateTime instead of DateTimeOffset. However, if I delete the table from the project in the designer and then readd it, the .NET type is changed to DateTimeOffset and all is well. Possible designer bug?

Are you sure? Unless you didn't select that table in the 'refresh catalog' dialog, LLBLGen should detect that type change. If the entity field type and the mapping type don't match you get a validation error. I can't reproduce that situation. If you have a reproducible example (db script + .llblgenproj file) we will take a look.

I do not have a reproducible example as we have decided to use UTC in a DateTime column. Just trying to clean up these open issues in my threads.