Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

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.

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

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 else you will get an error.

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

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.

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

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.

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

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))

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

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.