Convert "file time" varchar in hex format to datetime in T-SQL

I had a hard time finding an all-in-one conversion from a "file time" string to a datetime in T-SQL, so I pieced one together from several online hints.

To give you a little bit of background, I'm working with data that I dumped from an AD connector space using csexport, and attributes like accountExpires end up in a varchar column looking like this: 0x1ce2ea686b85800.  I needed a way, in T-SQL, to convert these to a datetime value.  I would have thought this would be something that's been done countless times before, but I couldn't quite find an all-in-one solution.

One quick note about this function.  It was enough for me to just convert to a date (year & day), as I mention in the comment, so please drop me a note if you add the timestamp to the conversion!

USE [FIMUtil]
GO

/****** Object: UserDefinedFunction [dbo].[FileTimeToDate]    Script Date:06/07/2013 15:19:18 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FileTimeToDate]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[FileTimeToDate]
GO

USE [FIMUtil]
GO

/****** Object: UserDefinedFunction [dbo].[FileTimeToDate]    Script Date:06/07/2013 15:19:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--=============================================
--Author:             Joe Zamora
-- Create date: 2013-06-07
-- Description: Convert a datetime value stored in hex format in a varchar type
-- to a datetime type. Currently only converts to the date (year & day) without
-- timestamp.
-- E.g. @FileTimeHex ='0x1CE2EA686B85800' or '1CE2EA686B85800'
--=============================================
CREATE FUNCTION [dbo].[FileTimeToDate] 
(
       -- Add the parameters for the function here
       @FileTimeHex varchar(50)
)
RETURNS datetime
AS
BEGIN
       -- Declare the return variable here
       DECLARE @Result datetime

       -- Add the T-SQL statements to compute the return value here
----DEBUG/TEST----
--declare @FileTimeHex varchar(50)
--set @FileTimeHex ='0x1CE2EA686B85800'
------------------
declare @LastLogin bigint
       ,@hexString varchar(50)
       ,@binValue varbinary(50)
-- Strip the leading '0x'
select @hexString =
       case when @FileTimeHex like '0x%' then substring(@FileTimeHex,3,18)
       else @FileTimeHex
       end
-- I guess we have to use a common length???
set @hexString = right('000000000000000000'+@hexString,16)
----DEBUG/TEST----
--select @hexString
--select convert(varbinary(50),@hexString,2)
------------------
set @binValue = convert(varbinary(50),@hexString,2)
----DEBUG/TEST----
--select @binValue
------------------
-- Bounds checking
set @LastLogin = null
select @LastLogin = 
       case when @binValue not in (0x0,0x7FFFFFFFFFFFFFFF) then @binValue
       end
----DEBUG/TEST----
--select @LastLogin
------------------
-- This formula taken from:
-- http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/3a6368c6-2580-4639-9604-a04cab08020d/
set @LastLogin = @LastLogin / (60 * 10000000)
set @LastLogin = @LastLogin / 1440
--set @LastLogin =@LastLogin -299
set @LastLogin = @LastLogin - (datediff(dd, '1/1/1900', dateadd(yy, 299, '1/1/1900')))

SELECT @Result = dateadd(day,@lastlogin,'1/1/1900')
----DEBUG/TEST----
--select @result
------------------

       -- Return the result of the function
       RETURN @Result

END

GO

Another update to FIM Powershell module for end-to-end attribute flows

Here's another tweak to the Get-FimSyncConfiguration.psm1 module to include export attribute flows with constant sync-rule values in the end-to-end attribute flows. See highlighted sections below.

Reference version:
FIM PowerShell Modules v2.1
http://fimpowershellmodule.codeplex.com/releases/view/98374

Get-ExportAttributeFlow

<#
  .SYNOPSIS 
  Gets the Export Attribute Flow Rules from Sync Server Configuration
  .DESCRIPTION
  Reads the server configuration from the XML files, and outputs theExport Attribute Flow rules as PSObjects
  .OUTPUTS
  PSObjects containing the synchronization server export attribute flowrules
  
  .EXAMPLE
  Get-ExportAttributeFlow -ServerConfigurationFolder"E:\sd\IAM\ITAuthorize\Source\Configuration\FimSync\ServerConfiguration"
#>
Function Get-ExportAttributeFlow
{
  Param
  (        
        [parameter(Mandatory=$false)]
        [String]
        [ValidateScript({Test-Path $_})]
        $ServerConfigurationFolder
  ) 
  End
  {    
        ### This is where the rules will beaggregated before we output them
        $rules = @()
        
        ### Export attribute flow rules arecontained in the ma-data nodes of the MA*.XML files
        $maFiles = @(get-item (Join-Path $ServerConfigurationFolder "MA-*.xml"))
        
        
        foreach ($maFile in $maFiles)
        {
            ### Get the MA Name and MA ID
            $maName = (select-xml $maFile -XPath "//ma-data/name").Node.InnerText
           
            foreach($exportFlowSet in (Select-Xml -path $maFile -XPath "//export-flow-set" | select -ExpandProperty Node))
            {
                $mvObjectType = $exportFlowSet.'mv-object-type'
                $cdObjectType = $exportFlowSet.'cd-object-type'
                
                foreach($exportFlow in $exportFlowSet.'export-flow')
                {
                    $cdAttribute = $exportFlow.'cd-attribute'
                    [bool]$allowNulls = $false
                    if ([bool]::TryParse($exportFlow.'suppress-deletions', [ref]$allowNulls))
                    {
                        $allowNulls = -not $allowNulls
                    }
                    
                    if ($exportFlow.'direct-mapping' -ne $null)
                    {
                        ###
                        ### Handlesrc-attribute that are intrinsic (object-id)
                        ###
                        if ($exportFlow.'direct-mapping'.'src-attribute'.intrinsic)
                        {
                          $srcAttribute = "<{0}>" -F $exportFlow.'direct-mapping'.'src-attribute'.'#text'
                        }
                        else
                        {
                            $srcAttribute = $exportFlow.'direct-mapping'.'src-attribute'
                        }
                    
                        $rule = New-Object PSObject
                        $rule | Add-Member -MemberType noteproperty -name 'RuleType' -value 'DIRECT'
                        $rule | Add-Member -MemberType noteproperty -name 'MAName' -value $maName                
                        $rule | Add-Member -MemberType noteproperty -name 'MVObjectType' -value $mvObjectType
                        $rule | Add-Member -MemberType noteproperty -name 'MVAttribute' -value $srcAttribute
                        $rule | Add-Member -MemberType noteproperty -name 'CDObjectType' -value $cdObjectType
                        $rule | Add-Member -MemberType noteproperty -name 'CDAttribute' -value $cdAttribute
                        $rule | Add-Member -MemberType noteproperty -name 'ScriptContext' -value $null
                        $rule | Add-Member -MemberType noteproperty -name 'AllowNulls' -value $allowNulls
                        
                        $rules += $rule
                    }
                    elseif ($exportFlow.'scripted-mapping' -ne $null)
                    {                
                        $scriptContext = $exportFlow.'scripted-mapping'.'script-context'                        
                        $srcAttributes = @()
                    
                        ###
                        ### Handlesrc-attribute that are intrinsic (object-id)
                        ###
                        $exportFlow.'scripted-mapping'.'src-attribute' | ForEach-Object {
                        if ($_.intrinsic)
                        {
                            $srcAttributes += "<{0}>" -F $_.'#text'
                        }
                        elseif ($_) # Do not addempty values.
                        {
                            $srcAttributes += $_
                        }
                    }
                    # (Commented)Leave as collection.
                    #if($srcAttributes.Count -eq 1)
                    #{
                    #    $srcAttributes = $srcAttributes -as[String]
                    #}
                        
                    $rule = New-Object PSObject
                    $rule | Add-Member -MemberType noteproperty -name 'RuleType' -value 'SCRIPTED'
                    $rule | Add-Member -MemberType noteproperty -name 'MAName' -value $maName
                    $rule | Add-Member -MemberType noteproperty -name 'MVObjectType' -value $mvObjectType
                    $rule | Add-Member -MemberType noteproperty -name 'MVAttribute' -value $srcAttributes
                    $rule | Add-Member -MemberType noteproperty -name 'CDObjectType' -value $cdObjectType
                    $rule | Add-Member -MemberType noteproperty -name 'CDAttribute' -value $cdAttribute 
                    $rule | Add-Member -MemberType noteproperty -name 'ScriptContext' -value $scriptContext
                    $rule | Add-Member -MemberType noteproperty -name 'AllowNulls' -value $allowNulls
                                    
                    $rules += $rule                        
                }
                    elseif ($exportFlow.'sync-rule-mapping' -ne $null)
                    {
                        $srcAttribute = $exportFlow.'sync-rule-mapping'.'src-attribute'
                        if($exportFlow.'sync-rule-mapping'.'mapping-type' -eq 'direct')
                        {
                            $rule = New-Object PSObject
                            $rule | Add-Member -MemberType noteproperty -name 'RuleType' -value 'OSR-Direct'
                            $rule | Add-Member -MemberType noteproperty -name 'MAName' -value $maName
                            $rule | Add-Member -MemberType noteproperty -name 'MVObjectType' -value $mvObjectType
                            $rule | Add-Member -MemberType noteproperty -name 'MVAttribute' -value $srcAttribute
                            $rule | Add-Member -MemberType noteproperty -name 'CDObjectType' -value $cdObjectType
                            $rule | Add-Member -MemberType noteproperty -name 'CDAttribute' -value $cdAttribute                                                    
                            $rule | Add-Member -MemberType noteproperty -name 'ScriptContext' -value $null
                            $rule | Add-Member -MemberType noteproperty -name 'AllowNulls' -value $allowNulls
                                            
                            $rules += $rule             
                        }
                        elseif ($exportFlow.'sync-rule-mapping'.'mapping-type' -eq 'expression')
                        {
                            $scriptContext = $exportFlow.'sync-rule-mapping'.'sync-rule-value'.'export-flow'.InnerXml
                            $srcAttribute = $exportFlow.'sync-rule-mapping'.'sync-rule-value'.'export-flow'.dest
                            $rule = New-Object PSObject
                            $rule | Add-Member -MemberType noteproperty -name 'RuleType' -value 'OSR-Expression'
                            $rule | Add-Member -MemberType noteproperty -name 'MAName' -value $maName
                            $rule | Add-Member -MemberType noteproperty -name 'MVObjectType' -value $mvObjectType
                            $rule | Add-Member -MemberType noteproperty -name 'MVAttribute' -value $srcAttribute
                            $rule | Add-Member -MemberType noteproperty -name 'CDObjectType' -value $cdObjectType
                            $rule | Add-Member -MemberType noteproperty -name 'CDAttribute' -value $cdAttribute                                                    
                            $rule | Add-Member -MemberType noteproperty -name 'ScriptContext' -value $scriptContext
                            $rule | Add-Member -MemberType noteproperty -name 'AllowNulls' -value $allowNulls
                                            
                            $rules += $rule             
                        }
                        elseif ($exportFlow.'sync-rule-mapping'.'mapping-type' -eq 'constant')
                        {                       
                            $srcAttributes = @()
                            $scriptContext = $exportFlow.'sync-rule-mapping'.'sync-rule-value'
                            $rule = New-Object PSObject
                            $rule | Add-Member -MemberType noteproperty -name 'RuleType' -value 'OSR-Constant'
                            $rule | Add-Member -MemberType noteproperty -name 'MAName' -value $maName
                            $rule | Add-Member -MemberType noteproperty -name 'MVObjectType' -value $mvObjectType
                            $rule | Add-Member -MemberType noteproperty -name 'MVAttribute' -value $srcAttributes
                            $rule | Add-Member -MemberType noteproperty -name 'CDObjectType' -value $cdObjectType
                            $rule | Add-Member -MemberType noteproperty -name 'CDAttribute' -value $cdAttribute                                                    
                            $rule | Add-Member -MemberType noteproperty -name 'ScriptContext' -value "'$scriptContext'"
                            $rule | Add-Member -MemberType noteproperty -name 'AllowNulls' -value $allowNulls
                                            
                            $rules += $rule             
                        }
                        else
                        {
                            throw "UnsupportedExport Flow type '$($exportFlow.'sync-rule-mapping'.'mapping-type')'"
                        }
                           
                    }
                }
            }
        }
        
        Write-Output $rules
    }#End
}