1dent1ty cHa0s

Had to wrap my brain around this one just recently – the FIM web service requires that you submit any datetime attributes with a very specific format with absolutely no wiggle room. This presents somewhat of a challenge when you are sourcing datetime values from multiple sources like MS SQL, Oracle and LDAP directories like AD/AD LDS.

When writing to the FIM web service the date needs to appear in this ISO 8601 format:

yyyy-MM-ddTHH:mm:ss.fff

For example:

2009-11-06T07:00:00.000

Now, there is no magic SQL or .NET function I've found that gets you precisely the format you need so you have to do some manipulation yourself in order to get it to export properly. While we're here, let's look at some additional caveats regarding dates with FIM:

• If the value is coming from the Synchronization Service (MIIS/ILM) then it needs to originate as a string data type – there are no datetime types in the metaverse, so you will find yourself converting datetime values to string types.
• The portal expects the time to be in UTC (GMT or Zulu) time, so you need to adjust the time to your own time zone before submitting it otherwise the portal will adjust it when presenting it to you through the portal. For instance, we're in the special Arizona (MST/GMT-7) time zone, so to adjust a midnight timestamp I have to add 7 hours and then the FIM portal will display it to me as midnight.
• The web service requires the fractional seconds component to be present (true as of RC1 Update 2) – if you don't submit the fractional seconds it will reject it.
• The web service requires precisely three digits of fractional precision to be specified; however, SQL itself is only so precise. You can generally submit the first two digits but you should zero the third if you require fractional precision, otherwise, zero all three (.000). If you don't get the precision correct, you will end up with confirming export errors since the third digit will shift.
• You can't specify timezone or any 'z' components

.NET Conversion

To do this in .NET you can adapt the following code into a handy function:

DateTime dtFileTime = DateTime.FromFileTime(csentry[strSourceAttribute].IntegerValue);
// Convert to UTC, format string using custom format similiar to round trip "o" format
// NOTE: SQL's precision for fractional time makes storage and confirmation of anything more than two digits problematic
// It's better to simply enforce .000 for fractional time here since it's not absolutely critical
mventry[strDestinationAttribute].Value = dtFileTime.ToUniversalTime().ToString("yyyy'-'MM'-'dd'T'HH':'mm':'ss'.000'");

TSQL Conversion

To do the conversion in TSQL it's a bit more difficult as I can't seem to find any functions that will automatically adjust the timezone for you. In any case, the approach is similar:

SELECT
--source attribute is already datetime/datetime2
[DATE_OF_HIRE] = CONVERT(nvarchar(30), DATEADD(hour, 7, [DATE_OF_HIRE]) , 126) + '.000'
--source attribute needs to be converted to datetime2
,[TERMINATION_DATE] = CONVERT(nvarchar(30), DATEADD(hour, 7, CAST([TERMINATION_DATE] AS datetime2(7))), 126) + '.000'
FROM tMyHRSource

Anyone who has worked with me on projects know I prefer doing my data manipulation prior to doing any imports to the Synchronization Service so I love the SQL approach and I apply this transform so that my dates appear in the connector space already in the proper format. When this is coming from a sources like AD you don't have that luxury so you need to apply this in a rules extension.