How do I properly convert SQL datetime into C# datetime?

Question

I have data stored in a SQL database that I'm attempting to read into an ASP.NET MVC application. I can get the data just fine - but the datetimes are not translating into anything useful.

Here's what I have so far (some data redacted as "..."):

    public JsonResult GetXYZByStatusJson()
    {
        var sqlConn = new SqlConnection(...);
        var command = new SqlCommand("...", sqlConn);

        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.AddWithValue("@status", 0);

        sqlConn.Open();
        var sqlResult = command.ExecuteReader();

        var collection = new Collection<Object>();
        while (sqlResult.Read()) 
        {
            collection.Add(new XYZ(
                ...
                Convert.ToDateTime(sqlResult["DateSent"]),
                Convert.ToDateTime(sqlResult["DateDue"]),
                ...
                ));
        }

        sqlResult.Close();
        sqlConn.Close();

        return Json(collection);
    }

The resulting JSON is formatted fine, but the dates look like "\/Date(1294120800000)\/"

How do I properly cast the SQL datetime into a C# DateTime? Using .ToString() doesn't have any affect.

By SQL do you mean SQL Server? If not which database server are you using? — Jim Blizard, Jan 06 '11 at 19:42
How do you keep your datetime values in database? Which type do you use? — HABJAN, Jan 06 '11 at 19:43
@Jim - since he's using an `SqlCommand` it's safe to assume SQL Server — Eric Petroelje, Jan 06 '11 at 19:44

score 2 · Accepted Answer · edited May 23 '17 at 12:04

2

There are nothing wrong with the conversion between Sql Server and C#.

The problem is how JsonResult formats the string.

The following SO question shows how you can process it at client side: How do I format a Microsoft JSON date?

The other way is to create your own alternative to JsonResult

edited May 23 '17 at 12:04

Community

1
1

answered Jan 06 '11 at 19:57

jgauffin

99,844
45
235
372

+1 and the server side http://blogs.microsoft.co.il/blogs/pini_dayan/archive/2009/03/12/convert-objects-to-json-in-c-using-javascriptserializer.aspx use the deserialize method though http://stackoverflow.com/questions/401756/parsing-json-using-json-net – user44298 Jan 06 '11 at 19:59
I just left that comment on @Nicklamort's answer a second ago. Good job seeing that. – arthurakay Jan 06 '11 at 20:02
Also, you are at the wrong site if you start pointing fingers at the ones trying to help you... – jgauffin Jan 06 '11 at 20:32

score 0 · Answer 2 · answered Jan 06 '11 at 19:52

0

If it is SQL Server, sqlResult["DateSent"].ToString() will give you something like this: "6/9/1999 12:00:00 AM, 8/15/1999 12:00:00 AM"

Use the built-in string methods (.IndexOf(), .Remove(), etc), or create your own, to parse whatever you need out of this string.

answered Jan 06 '11 at 19:52

Nick Rolando

25,879
13
79
119

I'm sorry, the example string I gave you has two datetimes in it. It should just look like this: "6/9/1999 12:00:00 AM" – Nick Rolando Jan 06 '11 at 19:54
1

Hmmm... I think my problem actually lies in the JSON serialization. Stepping through the debugger, it looks like the value is correctly cast into DateTime - but the UI still ends up with this garbled value. – arthurakay Jan 06 '11 at 20:01

How do I properly convert SQL datetime into C# datetime?

2 Answers2