mrswombat Posted November 3, 2011 Posted November 3, 2011 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.
doughemi Posted November 3, 2011 Posted November 3, 2011 Create a new calculation field DateAsDate = GetAsDate(YourTextDateField)
mrswombat Posted November 3, 2011 Author Posted November 3, 2011 Is this calculation applicable to FM Pro 9, because I don't see the "DateAsDate" function listed...
Vaughan Posted November 3, 2011 Posted November 3, 2011 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.
comment Posted November 3, 2011 Posted November 3, 2011 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?
doughemi Posted November 3, 2011 Posted November 3, 2011 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.
mrswombat Posted November 3, 2011 Author Posted November 3, 2011 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"
mrswombat Posted November 3, 2011 Author Posted November 3, 2011 Ok, I tested again and the fact that the text field date is set in the "June 30, 2011" format is definitely the culprit. Any other ideas? Thank you again...
comment Posted November 3, 2011 Posted November 3, 2011 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...
Vaughan Posted November 4, 2011 Posted November 4, 2011 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.
LaRetta Posted November 4, 2011 Posted November 4, 2011 But Vaughan ... doughemi said, "Create a new calculation field DateAsDate = GetAsDate(YourTextDateField)" Touching the original text-date field was not mentioned. :wink3:
mrswombat Posted November 8, 2011 Author Posted November 8, 2011 Wow Comment that calculation worked like a charm. Thank you all for the help again!
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now