Convert current timestamp to a decimal number with 10 digits

It would depend on how you want the time represented. For example. the number of elapsed seconds since some arbitrary start date.

Jim

I am not sure if you mean the arbitary date something like  01-01-1900.

But here is an example of a decimal, whcih I am told represents a time.
4557350997

I would like to know what time it actually represents.
I need to convert current timestmap to some number

That doesn't look like elapsed seconds - if so, it would be approximately 144.41 years. If you can find the representation from whatever source provided the number, it can be done. If you can get number-datetime pairs for some examples, It would probably be possible to deduce the algorithm to produce the numbers you need. Without one of those, it will be very difficult.

Jim

the source is from a hexadecimal timestamp that I get from a db query. The timestamp is tored as Binary(8)
the following is the select
SELECT MIN(XTimeStamp) FROM TableData (nolock) WHERE LastModifyTime BETWEEN '01/28/2008' AND '01/28/200812:00:00.000'

and this is what it returns in hexadecimal format
000000010FA3A855

Which I then convert to decimal to give me
4557350997

So what I want to do is enter a decimal timestamp and do the reverse, meaning pass it aa a Binary(8) to a stored proc for processing

Do you know which application currently writes XtimeStamp to the database? We know (or can find) the LastModify date and time. However, that doesn't necessarily bear a known relationship to XTimeStamp.

Jim

Xtimestamp is just a column name in the table TableData, the column definition is Binary(8)

you can explicitly convert from integer to varbinary:

select convert(varbinary(8),convert(bigint,4557350997))

result: 0x0A00000155A8A30F

The second cast was necessary because SQL apparently does not convert the constant to a bigint implicitly, so the result was different

I need to take a timstamp example DateTime.Now and convert it a decimal with 10 digits

I understand XTimeStamp is a column name. If there is data in that column, some application wrote it. That application presumably does the conversion you want. If you can find out from the authour of that application what algorithm is used to create the number, we can help you reproduce it.

Jim

Jim,

All the information that I ahve is that the field is stored as binary(8) , when I query it I get something like this 0x000000010FA3A855.

So is it possible to do something like this and arrive to a decimal
DateTime.Now.Year * (some num) + DAteTime.Now.Month * (some num) + DateTime.Now.Day * (some number) + DateTime.Now.Hour * (some num) + DateTime.Now.Min * (some num) +  DateTime.Now.Sec * (some num)

Jim

I have no idea how those values are stored, I will ask the team soon.
If I do not have an answer by Tuesday, i will award you the points for all the above explantion you provided.

Thanks again for all your help

No rush - I would like to see it working for you.

Jim