Dates stored as strings, need to find and convert to date format.

Hi meischris39,

I would use Python to fix this. Using a PythonCaller you can loop through all attributes and check if they contain a specific set of characters. If so, you can use some string manipulations or the datetime-library to convert to the correct date format. Below you can find a script to start with. If you can provide me some of the formats, I can try to implement them for you.

I do not know how you could automatically change the writer. You could of course use the 'automatic'-option for the attributes.

Good luck!

import fme
import fmeobjects

# Template Function interface:
def convertDate(feature):
    
    # Retrieve all attributenames of this feature
    attributes = feature.getAllAttributeNames()
   
    # loop through all attributes
    for cAttribute in attributes:
        
        # Make sure nothing is changed on the standard (hidden) fme variables
        if cAttribute[0:4] == 'fme_':
            continue
        
        # Get the value of this attribute
        value = feature.getAttribute(cAttribute)

	# Check if this attribute is a date
	if 'DT' in value or 'DATE' in value:
		# CHANGE THIS CODE TO CHANGE THE DATE TO THE CORRECT FORMAT
		newValue = value
		feature.setAttribute(cAttribute, newValue)	 


     # Output this feature towards the Workbench
     self.pyoutput(feature)

Try the AttributeExploder transformer. It will explode your attributes into individual features each with the name and value of a specific attribute. Then run the Tester on this data to look for "date" attributes (eg _attr_name contains "date").

Then turn that back into an attribute using an FMEFunctionCaller with

@SupplyAttributes(@Value(_attr_name),@Value(_attr_value))

.... then I'm not sure. You would somehow turn that into the correct date format (preferably inside that function caller), then use a FeatureMerger to get it back onto an unexploded set of data.

The whole thing would be dynamic too. But that's OK I think, because we're dealling with generic attribute names like _attr_name and _attr_value.

Hope this helps.

Ah great this looks great trying it now. In terms of the format it will just be the standard oracle date format that can be wrote.

The string is yyyymmdd ideally if we could validate the date at this point too to see whether it is actually a legitimate gregorian date that would be awesome.

If you pass your features through the DateFormatter, you can specify a particular value for invalid dates. This makes it easy to filter them out later using e.g. a Tester.

Yeah thats all working great now, problem is i can't workout what the FMEFunctionCaller is doing, if anything at all? The output following it from an inspector just shows a blank field.

After this, i still have the problem that the Inspector is picking up the value as a String and not a date.

Thanks for all the help so far guys!

It does sound like the issue is less about the actual data (if it is YYYYMMDD stored as a string in the original Geodatabase, then it will write out to Oracle date fields just fine). Instead, it sounds like the issue is the Oracle table definitions themselves -- you need the fields that have *DATE* or *DT* in them to be defined as Date columns and not string columns. As Takashi points out, this can be done via Dynamic Workflows in modern FMEs. In FME 2014 I'd be more tempted to do some kind of post-process to alter the tables by using the SchemaReader as the source, pointing to Oracle, then looking at the attributes on the schema and ALTERing any tables that had these. Maybe I'd ust Python ala the post above to create teh SQL Statement, and then execute it via SQLExecutor.