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.
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.
BTW The update statement is based upon the existing field being in MM/DD/YY format already even though it is VARCHAR.
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.
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.
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.
Actually these are not hard to explain the first time - these are essentials. The ‘How To’ guide lines explain most of this.
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)
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).
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.
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.
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
Gotta be something wrong over there. Your Java is old too. After all this consider updating to at least LO 5.3.x & Java 1.8.x
Check your table columns vs mine.
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)
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.