Jump to content
Claris Engage 2025 - March 25-26 Austin Texas ×

This topic is 4780 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Posted

Hello,

I'm working in a database that, for whatever inane reason, has a date field that was created as a text field. Now, years later, I have to do a find for a range of dates (an entire fiscal year, over 1,000 records) using this text field and of course, it's not working.

I can't change the field to a date field without messing up the data from thousands of previous records (or can I?), and I can't figure out if there is a way to create a date field to replace this text field, while pulling in the values from this text field.

Help please and thank you for any ideas or suggestions.

Posted

Create a new calculation field DateAsDate = GetAsDate(YourTextDateField)

Posted

No, don't do that: the original data could be lost.

I'd create a new calculation (date) field in the database and initially set it to GetAsDate(YourTextDateField). This will possibly clean up 80%.

Then go through the records and identify those that are not correct because of data entry issues. Craft the calculation to deal with these.

For instance one record might have 1 Jan 2010 entered into it. The calculation would be changed to

Case(

field = "1 Jan 2010" ; Date( 1 ; 1 ; 2010 ) ;

GetAsDate(YourTextDateField)

)

Alternatively if there is just one record with the bad date then correct the date field itself.

Rinse and repeat until all records are done. Then copy the calculation and paste it into the Replace command and use it to replace the original date field. Delete the new calc field.

Backup before, during and after doing this cleanup.

Posted

a database that, for whatever inane reason, has a date field that was created as a text field.

You left out the most important detail: in what format are the "dates" entered in this field?

Posted

Is this calculation applicable to FM Pro 9, because I don't see the "DateAsDate" function listed...

DateAsDate is the name of a calculation field, not a function.

Vaughan, how would this lose data?

You and comment are right: I assumed that the date is in the format mm/dd/yyyy or mm/dd/yy; bad move on my part.

Posted

Unfortunately, the date in the text field is in a "June 30, 2011" type of format..perhaps this is why my calculation field is not working? I tried what Vaughn said to do and I keep getting either a "?" or a strange "Sat0, 0000"

Posted

Ok, I tested again and the fact that the text field date is set in the "June 30, 2011" format is definitely the culprit. :idot:

Any other ideas? Thank you again...

Posted

Make the calculation field =


Date (

Ceiling ( Position ( "janfebmaraprmayjunjulaugsepoctnovdec" ; Left ( Textfield ; 3 ) ; 1 ; 1 ) / 3 ) ;

MiddleWords ( Textfield ; 2 ; 1 ) ;

RightWords ( Textfield ; 1 )

)

Set the result type to Date. Next, perform a find for ? in the calculation field - this will show records that do not translate to a valid date. If everything looks fine, change the calculation field's type to Date. I would also keep the old data as is, since with thousands of records you never know...

Posted

Vaughan, how would this lose data?

You're overwriting the source data with the result of the calculation and the source data is gone forever.

If the date is invalid it may end up as "?" but at least you know it's wrong. If the calculation ends up changing 1/1/49 to 1/1/2049 instead of 1/1/1949 how will you tell? It's a silent failure.

The other thing that Comment alluded to is the possibility of variations of date entry due to regional locations. The solution you create for a client in US probably won't work for a client in Japan or Australia because the date format is different.

Posted

But Vaughan ... doughemi said, "Create a new calculation field DateAsDate = GetAsDate(YourTextDateField)"

Touching the original text-date field was not mentioned. :wink3:

This topic is 4780 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.