Changing text field to date field

[Jondb]

Hi-

I have a text field where I have stored dates in the format ddmmyy. I want to change this to a date field (I know, I should have done that from the start) without losing 10 years worth of entries in this field.

 

I'm guessing if I have a formula to re-format all the data from ddmmyy to dd/mm/yy this would be enough to preserve the dates correctly when I switch the field type from text to date. 

 

Can anyone confirm if this is the case and give me a few pointers on how to write a script for this?

 

Thanks,

Jon

[liltbrockie]

Create a new date field

 

Hi-

I have a text field where I have stored dates in the format ddmmyy. I want to change this to a date field (I know, I should have done that from the start) without losing 10 years worth of entries in this field.

 

I'm guessing if I have a formula to re-format all the data from ddmmyy to dd/mm/yy this would be enough to preserve the dates correctly when I switch the field type from text to date. 

 

Can anyone confirm if this is the case and give me a few pointers on how to write a script for this?

 

Thanks,

Jon

 

If you mean the existing data is in the format dd/mm/yy then thats fine you can do it... if you seriously mean ddmmyy without any "/"'s then you have a bit of a problem.

[hbrendel]

First make a backup.

 

Now find all the records where your field (let's call it DateTextField) is not empty. Then place your cursor in the field and select 'Replace Field Contents...' in the 'Records'menu.

Select the third option (by calculation). In the calculation box enter:

Left ( DateTextField ; 2 ) & "-" & Middle ( DateTextField ; 3 ; 2 ) & "-" & Right ( DateTextField ; 2 )

Of course you replace 'DateTextField' with the actual name of the field.

 

Now click 'OK' and then 'Replace'.

 

After this you can change the field type.

 

Success! Don't forget the backup in case something goes wrong.

[eos]

You can use a calculation like 

Date ( 
Middle ( yourField ; 3 ; 2 ) ;
Left ( yourField ; 2 ) ;
2000 + Right ( yourField ; 2 ) // assuming that all your date are > 1999, as per “losing 10 years worth of entries” …
)

First make a backup of your database. Then create a new date field and test the calculation there, using Replace Field Contents. If everything's OK, convert your existing field to a date field, then again use Replace Field Contents to copy over the contents of the test field (then delete the test field). This saves you from having to re-direct existing scripts or calculations to a new field.

 

if you seriously mean ddmmyy without any "/"'s then you have a bit of a problem.

 

Why would that be?

[Jondb]

Excellent - I'll try those, thanks...

[keywords]

Two points:

1.   I think the key is to make sure you have the data in an acceptable date format BEFORE you switch the field type. For example, if the text data in the field is 27/02/14 FM will easily convert it to that same date, but if it is 270214, FM will leave it as is.

2.   A variation on the formula given by hbrendel is to make use of the Self function in place of the field name in your data replacement formula, and also use "/" as per your original post, instead of "–".

3.   The formula will only deliver a correct result if the text date is six digits (ie. 270214). Result will be wrong for any dates that don't have leading zeros on days and months (eg. 1314 instead of 010314).

OK, so it's three points!

[comment]

First make a backup.

First make a backup of your database.

make sure you have the data in an acceptable date format BEFORE you switch the field type

 

Here's another way that's simpler, safer and more fun in general:

 

1. Define a new calculation field (stored, result is Date) using the formula suggested by eos in post #4 above;

 

2. Perform a series of finds in the new calculation field searching for ? (invalid dates) and dates that are outside the expected range.

Fix the data (i.e. the original text data) in any records found;

 

3. Eyeball the dates in general, looking for any irregularities. Sorting by the calculation field may also lead to discovering irregularities - assuming the records were created in chronological order;

 

4. Once you are convinced the conversion was successful, change the type of the calculation field to Date. Check that the data in the field is still there and if it is, you can delete the original text field*.

 

5. Make sure you have a backup anyway.

 

 

---

(*) This is assuming there are no dependencies on this field - such as calculations, relationships, field validations, etc.

Edited February 27, 2014 by comment