How to force storage of datetime in milliseconds?

Question

Can anyone please advise on how to work around this issue? I need to convert DateTime into milliseconds since epoch and store that returned value in the database. The issue I am running into is that no matter what data type I specify the field to be, Azure automatically stores the returned value in actual date format - i.e., I get returned 1613800800000 correctly as I have it coded to do, but always gets stored in the database in this format no matter what, "Feb 20 2021 1".

protected void testButton_Click(object sender, EventArgs e)
{
    TimeSpan reversion = new TimeSpan(0, 0, 0);
    DayOfWeek weekday = DateTime.Today.DayOfWeek;


    if(weekday == DayOfWeek.Tuesday)
    {

        DateTime utcTime = DateTime.UtcNow;
        TimeZoneInfo centralZone = TimeZoneInfo.FindSystemTimeZoneById("Central Standard Time");
        DateTime centralTime = TimeZoneInfo.ConvertTimeFromUtc(utcTime, centralZone).AddDays(-3);
        DateTime centralTimeConvert = TimeZoneInfo.ConvertTimeFromUtc(utcTime, centralZone).AddDays(-3);

        centralTime = centralTime.Date + reversion;
        centralTimeConvert = centralTimeConvert.Date + reversion;

        var centralTimeMilliseconds = centralTimeConvert.ToUniversalTime().Subtract(
        new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc)
        ).TotalMilliseconds;

        System.Data.SqlClient.SqlConnection connect = new System.Data.SqlClient.SqlConnection(masterConnectionString);
        System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();

        command.CommandType = System.Data.CommandType.Text;
        command.CommandText = @"INSERT INTO StartEndTimes (StartTimeMilliseconds, StartTime) VALUES (@StartTimeMilliseconds, @StartTime)";

        command.Parameters.AddWithValue("@StartTime", centralTime);
        command.Parameters.AddWithValue("@StartTimeMilliseconds", centralTimeConvert);

        command.Connection = connect;
        connect.Open();
        command.ExecuteNonQuery();
        connect.Close();

        responseLabel.Text = centralTimeMilliseconds.ToString().Truncate(13);


    }

    else
    {
        

    }
}

I've tried specifying the data type as int, char, etc., and every other option so I'm hoping there's a way to hard code the storage format or some other way to stop the auto date formatting.

Can you post the table DDL and include a complete repro of the issue in your code? What you're describing doesn't sound possible. — David Browne - Microsoft, Feb 23 '21 at 14:14
A tick is 100ns. So to get milliseconds you can get the Tick and then divide by 10,000. — jdweng, Feb 23 '21 at 14:42
[Can we stop using AddWithValue() already?](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) — Hans Keﬆing, Feb 23 '21 at 16:43
There was an exception being thrown I wasn't aware of for data conversion that kept failing. I was able to get it to store the actual milliseconds value by doing this instead: string test = centralTimeMilliseconds.ToString().Truncate(13); — sffc, Feb 23 '21 at 17:00

score 0 · Answer 1 · answered Feb 23 '21 at 16:41

If a date-value is stored as one of the date data-types, it doesn't have a "format" but is stored as some binary value.

A format is only used when a conversion to text needs to happen, such as displaying that value to the user. Or when converting to JSON, which is a text format.

How to force storage of datetime in milliseconds?

1 Answers1