DateTime's representation in milliseconds?

I have a SQL-server timestamp that I need to convert into a representation of time in milliseconds since 1970. Can I do this with pla开发者_如何转开发in SQL? If not, I've extracted it into a DateTime variable in C#. Is it possible to get a millisec representation of this ?

Thanks,

Teja.

You're probably trying to convert to a UNIX-like timestamp, which are in UTC:

yourDateTime.ToUniversalTime().Subtract(
    new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc)
    ).TotalMilliseconds

This also avoids summertime issues, since UTC doesn't have those.

In C#, you can write

(long)(date - new DateTime(1970, 1, 1)).TotalMilliseconds

As of .NET 4.6, you can use a DateTimeOffset object to get the unix milliseconds. It has a constructor which takes a DateTime object, so you can just pass in your object as demonstrated below.

DateTime yourDateTime;
long yourDateTimeMilliseconds = new DateTimeOffset(yourDateTime).ToUnixTimeMilliseconds();

As noted in other answers, make sure yourDateTime has the correct Kind specified, or use .ToUniversalTime() to convert it to UTC time first.

Here you can learn more about DateTimeOffset.

There are ToUnixTime() and ToUnixTimeMs() methods in DateTimeExtensions class

DateTime.UtcNow.ToUnixTimeMs()

SELECT CAST(DATEDIFF(S, '1970-01-01', SYSDATETIME()) AS BIGINT) * 1000

This does not give you full precision, but DATEDIFF(MS... causes overflow. If seconds are good enough, this should do it.

This other solution for covert datetime to unixtimestampmillis C#.

private static readonly DateTime UnixEpoch = new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc);

public static long GetCurrentUnixTimestampMillis()
{
    DateTime localDateTime, univDateTime;
    localDateTime = DateTime.Now;          
    univDateTime = localDateTime.ToUniversalTime();
    return (long)(univDateTime - UnixEpoch).TotalMilliseconds;
} 

Using the answer of Andoma, this is what I'm doing

You can create a Struct or a Class like this one

struct Date
    {
        public static double GetTime(DateTime dateTime)
        {
            return dateTime.ToUniversalTime().Subtract(new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc)).TotalMilliseconds;
        }

        public static DateTime DateTimeParse(double milliseconds)
        {
            return new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc).AddMilliseconds(milliseconds).ToLocalTime();
        }

    }

And you can use this in your code as following

DateTime dateTime = DateTime.Now;

double total = Date.GetTime(dateTime);

dateTime = Date.DateTimeParse(total);

I hope this help you