Ask Your Question
1

base text_date_field to Base_Date_field Update/Convertion

asked 2015-03-30 08:38:53 +0200

MQ-818 gravatar image

Hi, I have a base table, where there is valid dates (fldBirthDateOLD) but the field type is Varchar/Text. I need this field (fldBirthDateOLD)to be Date Field (fldBirthDateNEW). I am not sure how to do this, since Update SQL stop's due to data type mismatch.

I have a feeling there is something very simple that I am missing that I can add to my SQL, so TEXT date can be converted and update into the correct DATE filed.

UPDATE "tblBirthDayDetail" SET "fldBirthDateNEW" = "fldBirthDateOLD"

Thanks in advance for your kind input and teaching.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2015-03-30 16:19:02 +0200

MQ-818 gravatar image

This might work.... I am going to try and update this posting afterwards

Bring the questionable table to calc and covert the text date to valid date save and back to base.

http://openoffice.blogs.com/openoffic...

edit flag offensive delete link more

Comments

THIS ONE ACTUALLY WORKED.....JUST COPY THE TABLE TO CALC AND FORMAT EACH COLUMN TO DATE (IF YOU HAVE A TIME FIELD THAT REQUIRES CHANGE FROM TEXT TO TIME THEN TAKE CARE OF IT WHILE YOU ARE AT IT) THEN COPY & PASTE TO BASE AS A NEW TABLE....BUT MANUALLY DO THIS PROCESS....GO STEP BY STEP AND MAKE SURE YOU CHOOSE THE NEW TABLE WITH CORRECT FIELD TYPE...THIS WAS A QUICK AND EASY FIX

MQ-818 gravatar imageMQ-818 ( 2015-03-31 08:36:28 +0200 )edit
0

answered 2015-03-30 16:16:36 +0200

doug gravatar image

updated 2015-04-01 14:56:28 +0200

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

image description

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.

image description

This is the table structure:

image description

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 (image description) to the left)

edit flag offensive delete link more

Comments

Thanks.... I will try it and post an update.

MQ-818 gravatar imageMQ-818 ( 2015-03-30 16:25:22 +0200 )edit

UPDATE / 3-30-15 @ 11:27 PST

I tried your SQL. But getting error message

         1: Wrong data type: java.lang.IllegalArgumentException
MQ-818 gravatar imageMQ-818 ( 2015-03-31 08:31:28 +0200 )edit

What is the format of the date in the VARCHAR field? The code is for a VARCHAR field in the format m/d/yyyy. If instead you are using a two-digit year, go to the RIGHT statement and change the 4 to a 2. If you are using a different separator, change the / throughout the snippet to your actual separator, e.g., -. If you have the month and day in a different order, for example, the European format, swap the positions of the first RIGHT and LEFT statements. Tested.

doug gravatar imagedoug ( 2015-03-31 14:20:17 +0200 )edit

It is VARCHAR. Format is MM/DD/YYYY.

Thanks for your help and continued support.

MQ-818 gravatar imageMQ-818 ( 2015-03-31 15:55:41 +0200 )edit

Tested ok for me on 'MM/DD/YYYY'. As this problem was solved already by alternative means, the only other suggestion I have would be to check the existing VARCHAR values for invalid dates or deviations from the format, especially instances of a two-digit year. VARCHAR of course would allow invalid dates and the SQL code for working around those problems would be complex. As you can see, some limitations in HSQLDB v.1.8 make the code cumberome as it is. Thx.

doug gravatar imagedoug ( 2015-03-31 17:41:34 +0200 )edit

I still can't run it.....Can you please help me to run it. I know it sounds stupid, that it is working in your machine but I can't run it.

So my TEXT table has them as VARCHAR what looks like MM/DD/YYYY and my new table with DATE format MM/DD/YYYY.

Lets make life simpler for me; use these names for SQL PLEASE... table1 , fldTxtDATE , fldDATE

MQ-818 gravatar imageMQ-818 ( 2015-04-01 06:52:06 +0200 )edit

Doug: Thanks for being such a good sportsman. Believe it or not after all these I still wasn't able to execute your code. Just bothering me. I was wondering Is there a way if I could email or upload the table for you to see where I am going wrong?

Thanks- Monjur MonjurQ@hotmail.com

MQ-818 gravatar imageMQ-818 ( 2015-04-04 03:21:16 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2015-03-30 08:38:53 +0200

Seen: 394 times

Last updated: Apr 01 '15