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





6 comments:

  1. Engineering as a subject combines mathematics, logic and science to find solutions to our daily life problems. Over the last few decades, engineering as a profession has seen vast expansion.jogos friv gratis 2019
    Jogos 2019
    jogos friv

    ReplyDelete
  2. สำหรับผู้ที่ชื่นชอบภาพยนตร์ และผู้ชมทุกท่าน ที่ชื่นชอบ ดูหนังใหม่ ซีรีย์ที่ชื่นชอบในการรับชมภาพยนตร์ ซีรีย์ ดูซีรีย์ออนไลน์ อัพเดทใหม่ตลอดทั้งเดือน

    ReplyDelete
  3. สำหรับผู้ที่ชื่นชอบภาพยนตร์ และผู้ชมทุกท่าน ที่ชื่นชอบ หนังออนไลน์ ซีรีย์ที่ชื่นชอบในการรับชมภาพยนตร์ ซีรีย์ ดูซีรีย์ออนไลน์ อัพเดทใหม่ตลอดทั้งเดือน

    ReplyDelete
  4. วันนี้จะมาแนะนำ ทดลองเล่นสล็อต megagame กับเว็บไซต์เราได้เเล้ววันนี้ที่ MEGA GAME ทุกท่านจะพบ กับเกมสล็อตออนไลน์ รูปแบบใหม่มีมากกว่า 1000 เกมที่เราได้คัดสรรมาให้นักเสี่ยงโชค

    ReplyDelete
  5. อยู่กับโลกออนไลน์ โดยเฉพาะแบรนด์ ดังอย่างเช่น betflix285 ของเรา Betflix เว็บคาสิโนออนไลน์ และ สล็อตออนไลน์ ที่ดีที่สุด ในเวลานี้ เพราะเรา betflix คือเว็บผู้ให้บริการเกม

    ReplyDelete
  6. วันนี้ เรา betflix888 ทางเข้า เว็บสล็อตออนไลน์ ชื่อดังอันดับหนึ่ง betflix มีเกม ให้สมาชิก ทุกท่านได้เลือกเล่นอย่างล้นหลาม โดยเฉพาะเกมสล็อต และ เกมคาสิโนออนไลน์

    ReplyDelete