none
Export EmployeeStartDate/EmployeeEndDate to SQL DateTime Field

    Question

  • Hello

    I've got a SQL MA to which I need to export the EmployeeStartDate and EmployeeEndDate from FIM.

    Could someone kind please point me in the direction of the VB I need to write some extension code to convert from the string held in the Metaverse (yyyy-mm-ddThh:mm:ss.000) to a SQL datetime?

    I appear to be channelling Winnie the Pooh this morning, and have very little brain. I can easily find out how to deal with this in the opposite direction...

    http://social.technet.microsoft.com/Forums/en-US/ilm2/thread/9ac65704-8a53-447a-b6a9-ebda777aa192

    (If I just leave FIM and SQL to work it out, I get an error: type-mismatch Schema problem [conversion] attribute startdate).

    Many thanks

    Jane

    Wednesday, May 30, 2012 10:32 AM

Answers

  • It's been awhile since I've played with that stuff in FIM (I'm back in ILM 2007 development for awhile) but since FIM doesn't have a datetime attribute type, I've always flowed dates into FIM in the format that the portal wants to see since the FIM MA only allows direct flows (which also makes the metaverse attribute human-readable unlike filetime and 64-bit integers).  That produces metaverse attributes of the format you're looking at.

    Along the lines of what Steve suggested, make sure that you are exporting a string as that is the most comparable between FIM data types and SQL datetime.  From my ILM provisioning code which creates SQL records when certain things occur:

    csentry("Date").Value = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")

    I've not had any problems with SQL doing it that way.  It seems you should be able to do a DateTime.Parse() on the metaverse attribute string value and then do the same .ToString() conversion I'm doing in my code.  There may be some adjustments needed to specify UTC (append a "Z" to your metaverse attribute) or a particular time zone...it is best to look at that in your own environment given the data that you're working with.  There are a lot of options with the DateTime class, not just with FileTime.

    Chris

    • Proposed as answer by UNIFYBobMVP Friday, June 01, 2012 9:18 AM
    • Marked as answer by SQLKnitter Tuesday, June 12, 2012 3:35 PM
    Thursday, May 31, 2012 6:41 PM

All replies

  • The appropriate DateTime format string is "yyyy'-'MM'-'dd HH:mm:ss".  (Make sure to use UTC times, as the SQL DATETIME datatype does not concern itself with timezone.)

    What usually works well is to store all date-times within FIM as 64-bit integers--the Windows FILETIME format which has some helpers in the DateTime type--and adjust for various input and output requirements via advanced flow rules.

    Wednesday, May 30, 2012 4:42 PM
  • It's been awhile since I've played with that stuff in FIM (I'm back in ILM 2007 development for awhile) but since FIM doesn't have a datetime attribute type, I've always flowed dates into FIM in the format that the portal wants to see since the FIM MA only allows direct flows (which also makes the metaverse attribute human-readable unlike filetime and 64-bit integers).  That produces metaverse attributes of the format you're looking at.

    Along the lines of what Steve suggested, make sure that you are exporting a string as that is the most comparable between FIM data types and SQL datetime.  From my ILM provisioning code which creates SQL records when certain things occur:

    csentry("Date").Value = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")

    I've not had any problems with SQL doing it that way.  It seems you should be able to do a DateTime.Parse() on the metaverse attribute string value and then do the same .ToString() conversion I'm doing in my code.  There may be some adjustments needed to specify UTC (append a "Z" to your metaverse attribute) or a particular time zone...it is best to look at that in your own environment given the data that you're working with.  There are a lot of options with the DateTime class, not just with FileTime.

    Chris

    • Proposed as answer by UNIFYBobMVP Friday, June 01, 2012 9:18 AM
    • Marked as answer by SQLKnitter Tuesday, June 12, 2012 3:35 PM
    Thursday, May 31, 2012 6:41 PM
  • Cheers: the combination of the two of you, and I'm happily flowing things around. Sorry for the delay in replying. In my other life, I'm a DBA...and that rather took over.
    Tuesday, June 12, 2012 3:36 PM