Saturday, June 8, 2013

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:
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





No comments:

Post a Comment