Drag and drop adds two days to date

ajardine · General User Joined: 19 Nov 2005 Posts: 22

I'm converting a personal financial application I had in Calc into Base.

I moved the records for the chequing account table by selecting The table in Calc and dragging it onto Base, which creates "Table1" to hold it. Then I use some SQL to move it into another table which is its new, permanent home.

One of the columns in calc was the transaction date. The column is in a date format. When I drag the data to Base, it adds two days to each of the dates. What's worse, is that HSQL doesn't support any date arithmetic, so it's not like I can just run some SQL to fix it. I can probably figure out how to write a macro in Calc to alter the date before I drag it over, but is there some reason Base is adding these two days?

trenkler · Power User Joined: 21 May 2005 Posts: 55

DrewJensen · Posted: Wed Aug 02, 2006 11:19 pm Post subject:

Well, there is good news.

The bug seems to finally be dead in the latest developers build, #178. I bleieve it was still there in 2.0.3. It seems OOo is on a pretty steady 3 month release cycle now, so 2.0.4 shouldn't be too long out if you want to wait for a stable release.

Otherwise I believe this workaround will help you now.

Create a Base database file that uses the spreadsheet as the external database. Then copy the table (drag and drop) from this ODB file to the native SQL ODB file. That should take care of your date shift.

Drew

ajardine · General User Joined: 19 Nov 2005 Posts: 22

Thanks Drew! This worked perfectly.

amberleadavis · Newbie Joined: 02 Sep 2009 Posts: 3

And I'm using oobase 3.1.0.

I already have a whole lot of data into the tables before I realized that the dates are each two days off. I've seen the problem before in excel. I think the problem may be with the date formats - but I don't know.

Anyone have any ideas? Idea

Thanks!

Villeroy · Posted: Thu Sep 03, 2009 10:15 am Post subject:

I wonder how this can happen in OOo3. What is the type of database and what about the spreadsheet?
Tools>Options...Calc>Calculation
Set day zero to 1900-01-01
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

amberleadavis · Newbie Joined: 02 Sep 2009 Posts: 3

THe spreadsheet is an openoffice CALC. I use the feature of dragging the data straight across.

I tried this in access and had similar issues, but I wasn't paying attention at the time.

I can give you some sample data.

I very much suspect that the problem may be with the original excel files. I opened them in excel, then updated them in oo.o The files were saved the the oo.o format. The excel field stores the dates in one method, that even going to access caused trouble - the same tow days.

What are your thoughts?

amberleadavis · Newbie Joined: 02 Sep 2009 Posts: 3

You may not need to do all these steps, but this is what I did and it worked.

I changed the format of the cells in calc to be:

mm/dd/yyyy

but the change that made the most difference was creating the table in oobase - with all the definitions - including telling oobase that the date was formatted as mm/dd/yyyy.

Then I appended the data.

No problems.