Converting datetime to bigint: Native formats: reverse engineering.

• thepotplants

  Hall of Fame

  Points: 3079

  More actions

  March 16, 2014 at 5:02 pm

  #286811

  Hi.

  I have a table containing a timestamp stored as a bigint. The database has a UDF which converts to a datetime perfectly.

  635167404000000000 = 2013-10-08 00:00:00.000

  635167836000000000 = 2013-10-08 12:00:00.000

  etc...

  The UDF seems to be an implicit conversion:

  CREATE FUNCTION [dbo].[ToDate](@value [bigint])

  RETURNS [datetime] WITH EXECUTE AS CALLER

  AS

  EXTERNAL NAME [xxxx].[UserDefinedFunctions].[ToDate]

  But I want to convert in the other direction. I want to take a datetime and convert it into timestamp with the same format as above.

  An implict conversion in the other direction gives a very different result.

  DECLARE @bi bigint

  SELECT @bi = convert(bigint, getdate())

  SELECT @bi

  =41713

  I found an article http://sqlmag.com/sql-server/solving-datetime-mystery) that talks about how datetime are stored, but can't see how that works.

  Can anybody please suggest how I can convert a datetime into this format?

  Thanks

  Pete

• Jeff Moden

  SSC Guru

  Points: 1003895

  More actions

  March 16, 2014 at 5:31 pm

  #1697525

  The function isn't any kind of "implicit" conversion. The function is based on a CLR.

  I have no idea what the time base for this function is. Normally these things come across as the number of milliseconds since 1970-01-01 00:00:00.000 but this one doesn't appear to have that time base. I've tried several others but no match.

  You need to find whomever built the CLR function and ask them. The might even have the reverse of the function already built into the CLR.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• thepotplants

  Hall of Fame

  Points: 3079

  More actions

  March 16, 2014 at 6:35 pm

  #1697528

  Thanks for your reply Jeff.

  You are absolutely right. I found the answer myself, by thinking "they must do this.. I wonder if they have already a function for it?" I had a poke through thier other UDF's and sure enough... I found this:

  CREATE FUNCTION [dbo].[ToBigInt](@value [datetime])

  RETURNS [bigint] WITH EXECUTE AS CALLER

  AS

  EXTERNAL NAME [xxx].[UserDefinedFunctions].[ToBigInt]

  GO

  I assumed it was an implicit conversion beause the code just says here's a bigint return it as a date or vice versa for this function.

  I don't understand how the CLR stuff works. But I guess somewhere it takes the input and converts it with some magic formula and returns the desired result.

  Out of interest, Is there anyway to unpick this and find out how it works? or would I need the source code? (.net?)

  Cheers

  Pete

• mister.magoo

  SSC-Forever

  Points: 47068

  More actions

  March 16, 2014 at 6:52 pm

  #1697529

  You could inspect the CLR with a tool such as ILSpy, but it looks like it is working out the number of ten millionths of a second since 1/1/0000 taking into account the changes to the calendar that have happened along the way...

  It's not something the date functions in SQL can deal with easily, hence the CLR (which is just a .NET assembly) to handle it.

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]