You would create the new date field/column alongside the existing VARCHAR field, and then UPDATE the new field with records from the old VARCHAR field. When satisfied, you would then DROP the old misformatted VARCHAR field. (You should make a backup copy of the database before executing the commands just in case).
To transfer data from the old column to the new, assuming a ‘m/d/yyyy’ VARCHAR format, with variable width of one or two characters for ‘m’ and ‘d’, the following SQL would work, executing on Tools → SQL, where fldTxtDATE is the VARCHAR field so formatted and fldDATE is a blank DATE field, and Table1 is the table containing both fields. The line spacing is purly optional for viewing the code. This converts the string to ‘yyyy-mm-dd’ which is a common DATE format for databases, and plunks the output into the new field. Note: this makes changes to your table that will be irreversible. Make sure the fldDATE column is blank.:
UPDATE
"Table1"
SET "fldDATE" =
CONCAT(
CONCAT(
CONCAT(
RIGHT("fldTxtDATE", 4), '-'
),
CONCAT(
LEFT("fldTxtDATE", LOCATE('/', "fldTxtDATE") - 1),'-'
)
),
SUBSTRING(
"fldTxtDATE", LOCATE('/', "fldTxtDATE") + 1,
LOCATE('/', SUBSTRING("fldTxtDATE", LOCATE('/', "fldTxtDATE")))
)
)
WHERE "fldDATE" IS NULL
AND LOCATE('/', RIGHT("fldTxtDATE", 4)) = 0
AND LENGTH(LEFT("fldTxtDATE", LOCATE('/', "fldTxtDATE"))) > 0
AND LENGTH(SUBSTRING(
"fldTxtDATE", LOCATE('/', "fldTxtDATE") + 1,
LOCATE('/', SUBSTRING("fldTxtDATE", LOCATE('/', "fldTxtDATE")))
)) > 0
Executing, looks like this (I ran it as Table2 but the code posted is as requested):
The error correction properties added in edit two will skip rows that are malformatted in some ways, although it is still posible to sent invalid dates to fldDate. Running on LO 4.4.1.2.
This is the table structure:
edit: added optional WHERE for safety, to ensure blank column
edit two: per comment, changed field names, screenshots, and added more error correction
(if this answered your question, please accept the answer by clicking the check mark (
) to the left)
