Ask Your Question
1

How to change field properties of already populated base table?

asked 2017-12-18 03:12:49 +0100

Walkinshaw gravatar image

Background; Way back I imported Calc data into Base but one of the columns - Date Issued - is a text(VARCHAR) field. I want to change properties of this column to Date to permit searching between dates. My initial attempts with ALTER TABLE "Table Name", ALTER COLUMN "Column Name" DATE; do not work. Any suggestions would be valued. Thanks.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
2

answered 2017-12-18 04:10:15 +0100

Ratslinger gravatar image

updated 2017-12-19 02:54:15 +0100

Hello,

Since your question states you cannot change table properties it suggests the table is either linked to a spreadsheet or it is tied to a .csv file (possibly originally generated from spreadsheet). On the main Base screen, in the lower left corner you should see either Text or Spreadsheet.

If it is Spreadsheet, the data resides in the location to the right of this as 'some_path/file_name.ods'. The field names & data types originate from there.

If it is Text, the data resides in a .csv file located again to the right of this as 'some_path'. In the .csv you would typically have the first line as field names and the remaining lines as data for the table. The key here is the actual format of the data to be recognized as a date. MM/DD/YY (or similar) is text - varchar. To be noted as a date it must be in the format of YYYY-MM-DD. If you make any changes to the .csv regarding this, you should do it with the Base file closed. If data is correctly modified (this means you should save a copy first) when you open the Base file again, the changes should be reflected. You cannot change just one for a test. It's all or nothing.

If, however, this is neither Text nor Spreadsheet, you need to provide more information on the database type involved - again on lower left of main Base screen.

Edit:

This statement works:

ALTER TABLE YOUR_TABLE_NAME ALTER COLUMN YOUR_FIELD_NAME DATE

This must be run from main screen of Base menu Tools->SQL.... Also, if the field you are changing already has data it must be in the correct format as expected by DATE: YYYY-MM-DD (hyphens included) else you will get an error.

Edit #2:

Additionally, if you want to automatically convert data, you will need to do this in multiple steps. First set up a new date field for the date to be placed into. Next run the following statement (from main screen of Base menu Tools->SQL...):

UPDATE YOUR_TABLE_NAME SET NEW_FIELD_NAME = to_date(OLD_FIELD_NAME, 'DD/MM/YY')

Once done & verified you can delete the old field.

Edit #3

Replace the previous update statement with this one:

UPDATE TOUR_TABLE_NAME SET NEW_FIELD_NAME = CAST(LEFT(OLD_FIELD_NAME,4) as varchar(4)) || '-' || SUBSTRING(OLD_FIELD_NAME FROM 5 FOR 2) || '-' || CAST(RIGHT(OLD_FIELD_NAME,2) as varchar(2))

Edit #4

Download this sample: ColumnModify.odb

Then apply this:

UPDATE "TABLE1" SET "INFOC" = CAST(LEFT("INFOB",4) as varchar(4)) || '-' || SUBSTRING("INFOB" FROM 5 FOR 2) || '-' || CAST(RIGHT("INFOB",2) as varchar(2))

Then check table for values in Column INFOC - What are the results?

If this answers your question please tick the ✔ (upper left area of answer). It helps others to know there was an accepted answer.

edit flag offensive delete link more

Comments

BTW The update statement is based upon the existing field being in MM/DD/YY format already even though it is VARCHAR.

Ratslinger gravatar imageRatslinger ( 2017-12-18 06:23:32 +0100 )edit

Thanks for great responses but I still have problems. My existing Date data is in YYYMMDD format without hyphens. This explains why my first attempt with ALTER TABLE, ALTER COLUMN was unsuccessful. A quick test on a test table with hyphenated date data works fine. But now I cannot convert my existing data (in YYYMMDD format) using your UPDATE statement because it needs to be in MM/DD/YYYY format. Converting thousands of fields manually is a scary thought. I will research SQL commands for this.

Walkinshaw gravatar imageWalkinshaw ( 2017-12-18 18:35:03 +0100 )edit

It's unfortunate every piece of information is arriving one at a time. Anything else to be known? Don't like re-writing answers because information is not complete.

Ratslinger gravatar imageRatslinger ( 2017-12-18 20:15:28 +0100 )edit

HSQL v. 2.3.2; LO v. 5.0.3.2, split database; My OS is LinuxMint KDE 17.3 if these are relevant. Your ALTER command requires date in YYYY-MM-DD but your UPDATE command - including 'DD/MM/YY' - requires existing field in MM/DD/YY format? I agree it is hard to explain everything first time. Anything specific you want? Your support is appreciated.

Walkinshaw gravatar imageWalkinshaw ( 2017-12-18 21:49:20 +0100 )edit

Actually these are not hard to explain the first time - these are essentials. The 'How To' guide lines explain most of this.

Ratslinger gravatar imageRatslinger ( 2017-12-18 22:24:09 +0100 )edit

I tried the new UPDATE

UPDATE "stampcatalogue_tbl" SET "Date_Issued" = CAST(LEFT("Issue_Date",4) as varchar(4)) || '-' || SUBSTRING("Issue_Date" FROM 5 FOR 2) || '-' || CAST(RIGHT("Issue_Date",2) as varchar(2))

and it gives the Status message

1: Wrong data type: java.lang.IllegalArgumentException Existing data is still in YYYYMMDD format and nothing shows in the new date column (Date Issued)

Walkinshaw gravatar imageWalkinshaw ( 2017-12-19 01:20:50 +0100 )edit

I have tried as best as can to duplicate your setup - HSQL v. 2.3.2; LO v. 5.0.6.3, split database (using JDBC) ; My OS is Linux Mint 18.3 Cinnamon even to the point of reloading LO (yours is very old). The LO version difference is minor. Works here without a problem. Even copied the code you posted & just modifiel names. I can only think of a couple of things. Edit your original question & post a screenshot of the table edit you are dealing with (new field included).

Ratslinger gravatar imageRatslinger ( 2017-12-19 02:34:50 +0100 )edit

That is a phenomenal effort on your part. Let me ponder this for a while and maybe update LO and my OS to see if those change anything before I get back.

Walkinshaw gravatar imageWalkinshaw ( 2017-12-19 02:50:47 +0100 )edit

Don't think those are the problem. If anything the LO version but I already tested with a similar one. It may not be a waste of time but I really believe it's not the actual problem. Try the test I sent. Also, post the Java version run in LO - from menu Tools->Options then under LibreOffice->Advanced will be in right center pane.

Ratslinger gravatar imageRatslinger ( 2017-12-19 02:56:43 +0100 )edit

Tried your ColumnModify.odb and bingo, results in Column INFO C are 03/12/17 01/12/16 12/12/15

So your code works on your .odb but not on mine yet

My Java JRE is Oracle Corporation version 1.7.0_151

Walkinshaw gravatar imageWalkinshaw ( 2017-12-19 04:06:46 +0100 )edit
0

answered 2017-12-18 04:33:36 +0100

Walkinshaw gravatar image

No Text or Spreadsheet in lower left. Instead, it is JDBC and to the right of that is hsqldb file location. (This is a split database)

edit flag offensive delete link more

Comments

Please do NOT use an answer (it is for response to original question only) for a response to an answer. Instead use a comment to the answer you are responding to.

Ratslinger gravatar imageRatslinger ( 2017-12-18 04:42:09 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-12-18 03:12:49 +0100

Seen: 436 times

Last updated: Dec 19 '17