MSSQL datetime issue with ofbiz UtilDatetime.getDayEnd function

[Bilgin Ibryam-2]

Hi all,

I have a problem with UtilDate.getDayEnd function used with MSSQL database.
Ofbiz timestamp fields on the forms usually have millisecond precision,
like this 2010-2-25 1:10:15.268
But when UtilDate.getDayEnd is used, it sets the nano seconds to
999999999 and the timestamp is stored in DB as 2010-2-25 23:59:59.999999999
And when MSSQL is used this timestamp gets rounded and becomes 2010-2-26
00:00:00.0 because MSSQL datetime field has accuracy of 3 milliseconds
-  http://msdn.microsoft.com/en-us/library/aa258277%28SQL.80%29.aspx   
ie there is no .999 but only .997 and then .000 milliseconds.

Any idea how to deal with this problem? Do we really need  nano second
precision for day end? May we should have it configurable so MSSQL users
can set a value of 990000000 in their instances

Thanks in advance.
Bilgin

[Adrian Crum-2]

We could set the nanoseconds to zero with a note that it is for MySql compatibility.

-Adrian

--- On Mon, 2/15/10, Bilgin Ibryam <[hidden email]> wrote:

> From: Bilgin Ibryam <[hidden email]>
> Subject: MSSQL datetime issue with ofbiz UtilDatetime.getDayEnd function
> To: [hidden email]
> Date: Monday, February 15, 2010, 3:25 AM
> Hi all,
>
> I have a problem with UtilDate.getDayEnd function used with
> MSSQL database.
> Ofbiz timestamp fields on the forms usually have
> millisecond precision, like this 2010-2-25 1:10:15.268
> But when UtilDate.getDayEnd is used, it sets the nano
> seconds to 999999999 and the timestamp is stored in DB as
> 2010-2-25 23:59:59.999999999
> And when MSSQL is used this timestamp gets rounded and
> becomes 2010-2-26 00:00:00.0 because MSSQL datetime field
> has accuracy of 3 milliseconds -  http://msdn.microsoft.com/en-us/library/aa258277%28SQL.80%29.aspx   ie
> there is no .999 but only .997 and then .000 milliseconds.
>
> Any idea how to deal with this problem? Do we really
> need  nano second precision for day end? May we should
> have it configurable so MSSQL users can set a value of
> 990000000 in their instances
>
> Thanks in advance.
> Bilgin
>

[Jacques Le Roux]

From: "Adrian Crum" <[hidden email]>
> We could set the nanoseconds to zero with a note that it is for MySql compatibility.
                                                                                                 MSSQL

Jacques
 

> -Adrian
>
> --- On Mon, 2/15/10, Bilgin Ibryam <[hidden email]> wrote:
>
>> From: Bilgin Ibryam <[hidden email]>
>> Subject: MSSQL datetime issue with ofbiz UtilDatetime.getDayEnd function
>> To: [hidden email]
>> Date: Monday, February 15, 2010, 3:25 AM
>> Hi all,
>>
>> I have a problem with UtilDate.getDayEnd function used with
>> MSSQL database.
>> Ofbiz timestamp fields on the forms usually have
>> millisecond precision, like this 2010-2-25 1:10:15.268
>> But when UtilDate.getDayEnd is used, it sets the nano
>> seconds to 999999999 and the timestamp is stored in DB as
>> 2010-2-25 23:59:59.999999999
>> And when MSSQL is used this timestamp gets rounded and
>> becomes 2010-2-26 00:00:00.0 because MSSQL datetime field
>> has accuracy of 3 milliseconds - http://msdn.microsoft.com/en-us/library/aa258277%28SQL.80%29.aspx ie
>> there is no .999 but only .997 and then .000 milliseconds.
>>
>> Any idea how to deal with this problem? Do we really
>> need nano second precision for day end? May we should
>> have it configurable so MSSQL users can set a value of
>> 990000000 in their instances
>>
>> Thanks in advance.
>> Bilgin
>>
>
>
>      
>

[Bilgin Ibryam-2]

Thanks for the solution Adrian. Done in trunk rev 912509

Bilgin

> We could set the nanoseconds to zero with a note that it is for MySql compatibility.
>
> -Adrian
>
> --- On Mon, 2/15/10, Bilgin Ibryam <[hidden email]> wrote:
>
>  
>> From: Bilgin Ibryam <[hidden email]>
>> Subject: MSSQL datetime issue with ofbiz UtilDatetime.getDayEnd function
>> To: [hidden email]
>> Date: Monday, February 15, 2010, 3:25 AM
>> Hi all,
>>
>> I have a problem with UtilDate.getDayEnd function used with
>> MSSQL database.
>> Ofbiz timestamp fields on the forms usually have
>> millisecond precision, like this 2010-2-25 1:10:15.268
>> But when UtilDate.getDayEnd is used, it sets the nano
>> seconds to 999999999 and the timestamp is stored in DB as
>> 2010-2-25 23:59:59.999999999
>> And when MSSQL is used this timestamp gets rounded and
>> becomes 2010-2-26 00:00:00.0 because MSSQL datetime field
>> has accuracy of 3 milliseconds -  http://msdn.microsoft.com/en-us/library/aa258277%28SQL.80%29.aspx   ie
>> there is no .999 but only .997 and then .000 milliseconds.
>>
>> Any idea how to deal with this problem? Do we really
>> need  nano second precision for day end? May we should
>> have it configurable so MSSQL users can set a value of
>> 990000000 in their instances
>>
>> Thanks in advance.
>> Bilgin
>>
>>    
>
>
>      
>