Convert BigInt value in SQL to datetime in c#

Question

I usually use the SQL function below to convert bigint in sql to datetime in SQL Server 2005.

DECLARE @datetime AS bigint, @day AS int, @ms AS int
SET @datetime = 129471410567460000 
SET @ms = (@datetime / CAST(10000 AS bigint)) % 86400000
SET @day = @datetime / CAST(864000000000 AS bigint) - 109207
SELECT DATEADD(ms,@ms, DATEADD(day, @day, 0))

How do i do it using c# ? I am able to read in time value (129471410567460000) from the SQL server and want to convert to datetime. The datetime will be used in a c# application.

Cheers

Btw, your tick value seems awfully low. For example, the tick value I get for today is 634401513869130569. — Thomas, May 05 '11 at 07:17

score 4 · Answer 1 · edited May 23 '17 at 10:34

4

Are you trying to convert a bigint in SQL Server to a datetime in C#? If so, you would do something like:

var tickValue = 129471410567460000;
var datetime = new DateTime( tickValue );

If however, you are tying to convert a bigint value in SQL Server to a datetime value in SQL Server then look at the following link:

Convert .NET Ticks to SQL Server DateTime

If you are trying mimic your exact logic (how you are getting that tick value is its own mystery):

var tickValue = 129471410567460000;
var ms = ( tickValue / 10000 ) % 86400000;
var day = tickValue / 864000000000 - 109207;

var startDate = new DateTime( 1900, 1, 1 );
var resultDateTime = startDate.AddMilliseconds( ms ).AddDays( day );

The trick to this logic is the start date. In SQL Server, day zero = '1900-01-01' even though DateTime values can store values going back to 1753.

In comments, you mentioned that the SQL method was posted in a forum. It is crucial that you know the method used to calculate the bigint value. The forum seems to suggest that this value is a Win32 FILETIME structure: that stores the date as 100-nanosecond intervals since 1601. If that is the case, the code you would use in C# is:

var startDate = new DateTime( 1601, 1, 1 );
var resultDateTime = startDate.AddTicks( tickValue );

You will note that this value returns 2003-05-14 4:51:56 PM which is the approximate date and time of the forum thread.

edited May 23 '17 at 10:34

Community

1
1

answered May 05 '11 at 07:13

Thomas

63,911
12
95
141

This gives me an invalid value.. Tried that.. Any idea? – ove May 05 '11 at 07:25
@overule - How are you getting that tick value? How is it determined? It is definitely not the equivalent of a .NET tick value. That said, I've posted an example of what would give you the equivalent in C# to your T-SQL code. – Thomas May 05 '11 at 07:30
In sql, it's a bigint column so using the same logic to convert back to datetime – ove May 05 '11 at 07:32
@overule - Same logic as what? A bigint can hold anything. What was the source datetime value? In SQL Server, if I convert a DateTime to a bigint, I get a five digit number not a huge long value. – Thomas May 05 '11 at 07:35
This forum will give you the explaination how those numbers are arrived. http://www.google.com/url?sa=t&source=web&cd=1&ved=0CCMQFjAA&url=http%3A%2F%2Fwww.sqlteam.com%2Fforums%2Ftopic.asp%3FTOPIC_ID%3D26544&rct=j&q=convert%20bigint%20to%20datetime&ei=81PCTeX7HcrZgQfRr9zoAQ&usg=AFQjCNHtv0Ia4YOoypF09uIl4yTx1NdVUQ&sig2=4WQ0c56igRyGjh9SMGvxew&cad=rja – ove May 05 '11 at 07:38

score 1 · Accepted Answer · answered May 06 '11 at 02:29

1

Solution is

DateTime.FromFileTimeUtc()

answered May 06 '11 at 02:29

ove

3,092
6
34
51

score 0 · Answer 3 · answered Sep 21 '16 at 05:52

0

public DateTime ConvertLongFormate2DateTime(long iDatelongValue)
{
    try
    {
        var iSecondCal = (iDatelongValue / 1000 + 8 * 60 * 60);
        var startDate = new DateTime(1970, 01, 01);
        DateTime resultDateTime = startDate.AddSeconds(iSecondCal);
        return resultDateTime;
    }
    catch (Exception)
    {
        throw;
    }
}

answered Sep 21 '16 at 05:52

Dharmik Chotaliya

1
1

A liitle explanation wouldn't hurt anyone. – croxy Sep 21 '16 at 06:16

score 0 · Answer 4 · answered May 05 '11 at 07:06

0

You could make it a stored procedure, then call that from C#.

answered May 05 '11 at 07:06

George Duckett

31,770
9
95
162

His question is how to do it in C# – V4Vendetta May 05 '11 at 07:11
My application don't have the access to database. Need to do in C# – ove May 05 '11 at 07:25

score 0 · Answer 5 · answered May 05 '11 at 07:16

0

Try this:

long datetime = 129471410567460000;
int ms = (int)((datetime / 10000) % 86400000);
int day = (int)(datetime / 864000000000L) - 109207;


DateTime dt = DateTime.MinValue.AddDays(day).AddMilliseconds(ms);

answered May 05 '11 at 07:16

Nathan

6,095
2
35
61

1

(not sure if DateTime.MinValue is the correct starting point) – Nathan May 05 '11 at 07:18
well, it's as accurate as the original function you gave. As you can see, that's accurate to the millisecond instead of the tick. – Nathan May 05 '11 at 07:36

Convert BigInt value in SQL to datetime in c#

5 Answers5