In the preceding article(http://www.loveayang.com.cn/post/2009/10/26/Embedded-Sqlitee28099s-Date-type-in-Java-and-DotNet.aspx) , I mentioned that the difference of Date Time in SQLite embedded version of DotNet and Java, I have not give out the solution, here is the solution for Java and DotNet.

You should know, this error is only occurred when you using Java code to access SQLite database generated by DotNet and vice versa, so our solution should only affect the code when using SQLite.

I made a mistake in the preceding article, the long value in Java convert to DotNet should be : longjavavalue*10000+DotNetTimeTicks19700101000000=DotNetTimeTicks

I missed some the important time zone issue, Java is using UTC time as it’s default time format, but DotNet is using local time, so we should consider time zone offset during convert time between them two.

But unfortunately, we could not catch all of those converting, for example, when we use SQL statement like “select * from datatable where datefield>’2009-12-12’”, we can not guarantee that the date string “2009-12-12” would works well, because the data in the table might could not match the really value we wanted.

It seems that we should do a totally database converting which convert all of the date columns to proper format before we use the database. I am working on that and I will share those in later articles.

OK, let’s check the solution what we got. Hopes it works.

Here is Java code to handle this

    //Notes that Java Time is UTC time, but DotNet time is local time,
    //So we must minus the time zone offset before return.
    public static java.util.Date DotNetTimeToJavaTime(long dotnetTimeticks)
    {
        if(TickFor197001010000UTC < dotnetTimeticks)
        {
            dotnetTimeticks = (dotnetTimeticks - TickFor197001010000UTC) / 10000;
            long timezoneoffset=java.util.TimeZone.getDefault().getOffset(dotnetTimeticks);
            dotnetTimeticks-=timezoneoffset;
        }
        return new java.util.Date(dotnetTimeticks);
    }
    //Notes that Java Time is UTC time, but DotNet time is local time,
    //So we must add the time zone offset before return.
    public static long JavaTimeToDotNetTime(java.util.Date date)
    {
        long ticks =null==date?0:date.getTime();
        ticks= ticks*10000L+TickFor197001010000UTC;
        long timezoneoffset=java.util.TimeZone.getDefault().getOffset(ticks);
        ticks+=timezoneoffset*10000L;
        return ticks;
    }

Here is the DotNet source code to handle this

//Notes that Java Time is UTC time, but DotNet time is local time
        public static long DotNetTimeToJavaTime(DateTime dt)
        {
            dt=dt.ToUniversalTime();
            return (dt.Ticks - TickFor197001010000UTC) / 10000;
        }
        //Notes that Java Time is UTC time, but DotNet time is local time
        public static DateTime JavaTimeToDotNetTime(long javaTimeTick)
        {
            if (javaTimeTick >= TickFor197001010000UTC)
            {
                return new DateTime(javaTimeTick);
            }
            else
            {
                javaTimeTick = javaTimeTick * 10000 + TickFor197001010000UTC;
                return new DateTime(javaTimeTick).ToLocalTime();
            }
        }