3

I have a string field that I want to convert into a date field. The problem is that some dates are incomplete (ex: "-/-/1967"; "1/2/-"). So is it possible to have in the same date field complete dates and others incomplete?

later edit: To make the conversion I use Data management>fields>Convert Time Field tool and the format I type is d/M/yyyy. My programming experience is poor, but I can learn. :)

Improve this question
8
  • How are you converting the date? It is my assumption that you will need to write a custom conversion piece as I would guess the - would cause importation issues.
    – Branco
    Jul 17, 2015 at 18:17
  • I'm using the Convert Time Field tool from data management>fields. And the format I'm typing is d/M/yyyy. I know this format implies that all the components af the date should be present...
    – panda
    Jul 17, 2015 at 18:28
  • The tool suggests you follow some of the specified formats (resources.arcgis.com/en/help/main/10.1/index.html#//…). I believe the tool with throw an exception on your values. It may be smart enough to throw in dummy dates, but I don't think that would be the case. How are your programming skills to write a custom date parser?
    – Branco
    Jul 17, 2015 at 18:32
  • 1
    Please edit the question in response to comments for clarification. Date parsers generally expect valid results; if you have invalid values, you'll need to pre-process them before they could be parsed.
    – Vince
    Jul 17, 2015 at 18:32
  • 1
    The answer is no for a date field. The incomplete dates would have to become complete dates to be stored in a date field and you would have to replace the "-"'s with numbers that make sense as a whole date. So -/-/1999 would become 1/1/1999 (1999 alone is an invalid date and I would assume 1 for any missing month or day) and 1/2/- would become 1/2/2015 (current year is assumed if you enter just 1/2 in a date field). A code block in Field Calculator would be required with conditional logic to recognize the different incomplete patterns and replace the "-" with numbers that complete the date.
    – Richard Fairhurst
    Jul 17, 2015 at 19:10

1 Answer 1

Reset to default
3

I quickly wrote this up for the Field Calculator. This will return a new string where your "-" character should be replaced.

Assumptions I am making on your data:

  1. Everything is DD/MM/YYYY
  2. Where there isn't a value, it is replaced with a non-numeric string

This uses the python parser in the Field Calculator. I suggest running the code on a new temporary field so if there are issues, it doesn't mess up your good data. You need to replace a few things in the script. Where you see the "INSERT VALUE..." you need to change that to a number for either day, month or year.

If you are very unfamiliar with how to use python in the Field Calculator, I suggest you read this ESRI article.

Here is a screenshot of the setup in ArcMap Be sure you watch the indentations of the python script when copy and pasting. Also, make sure you call the function in the *UniqueID = * section (from my image). Yours will have your temporary field name. Also, easiest to double-click your field when entering it into the dateCheck(!FIELDHERE!) so it inserts the ! points.

def dateCheck(inputDate):

    newDate = ""
    splitDate = inputDate.split("/")
    if (splitDate[0].isdigit()):
        newDate += splitDate[0]
    else:
        newDate += "INSERT VALUE FOR DAY"

    #insert the separator
    newDate += "/"

    if (splitDate[1].isdigit()):
        newDate += splitDate[1]
    else:
        newDate += "INSERT VALUE FOR MONTH"

    #insert the separator
    newDate += "/"

    if (splitDate[2].isdigit()):
        newDate += splitDate[2]
    else:
        newDate += "INSERT VALUE FOR YEAR"
    #return the newly constructed date
    return newDate

Basically, we are splitting your current date by the / character. Each of those gets stored into a list, which is accessible by the index (the 1,2,3 inside of the [] I am using).

Each of those values is checked against the python isdigit() function to see if it is really a number, or if it is a character like "-". If it passes, it is returned and appended to our new string. If it doesn't pass, you need to fill in a replacement value to be inserted instead.

You will still need to run the tool to convert to a date. I only did the work with the string for now since there is already a tool to convert string to date. This could all have been done using python into a new date field, too.

I haven't tested this with data, but give it a shot. Do a bit of reading up on python, and it will definitely help you out with these little challenges. Post a comment if there are problems.

Improve this answer
8
  • thank you so much for the effort! and for the headache :)).. I hope this will work for me:)
    – panda
    Jul 17, 2015 at 19:13
  • you said "If it doesn't pass, you need to fill in a replacement value to be inserted instead". The problem is that I don't want to fill it with fake information. I wanted to know if a cell from a date field can store an incomplete date. But I guess that's not possible.. Thanks again for the suggestions and the effort!
    – panda
    Jul 17, 2015 at 19:25
  • 2
    Yeah, the only way to store the incomplete record is in a text field. Date fields require proper dates. Is there a reason you want to use a date field with incomplete dates? They would be pretty useless for things like querying, etc.
    – Branco
    Jul 17, 2015 at 19:28
  • 1
    In that case, I would just stick with the text field. Maybe substitute the missing date info with question marks or some other character that shows the date couldn't be fully determined. Does your client have a specific notation they would like to have in places where it cannot be determined?
    – Branco
    Jul 17, 2015 at 19:43
  • 1
    I've done a bit of work in cemeteries, and this is definitely a difficult issue. For example, enabling time on the layer is really great (to animate the pattern of interments over time), but that can only be done with a proper date field. It worked ok to substitute '1' for missing day or month, but obviously not for year. also some graves just say "aug. 1943". I'd recommend a second 'date' field, in addition to the real field. In one instance I made 3 fields, 'day', 'month' and 'year' and then concatenated dates from there when I needed a label...
    – mr.adam
    Jul 17, 2015 at 21:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.