Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Converting datetime to bigint: Native formats: reverse engineering. Expand / Collapse
Author
Message
Posted Sunday, March 16, 2014 5:02 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 8:25 PM
Points: 64, Visits: 311
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
Post #1551610
Posted Sunday, March 16, 2014 5:31 PM This worked for the OP Answer marked as solution


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:36 AM
Points: 36,995, Visits: 31,524
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1551612
Posted Sunday, March 16, 2014 6:35 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 8:25 PM
Points: 64, Visits: 311
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
Post #1551615
Posted Sunday, March 16, 2014 6:52 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 12:18 PM
Points: 1,787, Visits: 5,699
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


  • MMGrid Addin
  • MMNose Addin


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

  • Post #1551616
    Posted Thursday, June 26, 2014 5:51 PM
    Valued Member

    Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

    Group: General Forum Members
    Last Login: Wednesday, August 6, 2014 8:25 PM
    Points: 64, Visits: 311
    mister.magoo (3/16/2014)
    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...


    Cool :) thanks for the tip. I must go back and have another look at that.
    Post #1586691
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse