How to change default string representation of GETDATE() to include milliseconds?

Question

I'm creating an tool called dbfiddle, and someone has asked how to get more precision from GETDATE() and SYSDATETIME(), which currently show seconds only:

SELECT SYSDATETIME(), GETDATE();
GO

(No column name)    | (No column name)   
:------------------ | :------------------
25/03/2017 14:22:16 | 25/03/2017 14:22:16

dbfiddle here

I'm using a JSON API to the database backends, and SQL Server 2014/2016 use System.Web.Script.Serialization.JavaScriptSerializer to convert the results to JSON, producing this output:

[["25/03/2017 13:59:54"],["25/03/2017 13:59:54"]]

The results are produced simply by building an array from ExecuteReader and passing that to JavaScriptSerializer.

I think I need to alter DateTimeFormat.LongTimePattern of the current culture to display milliseconds (or more), is that right, and if so, how do I do that for an ASP page in VB.NET?

I can't alter the SQL getting executed so CONVERT is not an option.

Here is a simplified version of the relevant portion of my code:

Dim sr = New StreamReader(Request.InputStream)
Dim ser = New System.Web.Script.Serialization.JavaScriptSerializer()
ser.MaxJsonLength = Int32.MaxValue
Dim queries = ser.Deserialize(Of List(Of String))(sr.ReadToEnd())
sr.Close()
Dim query = queries(0)

Dim connection As SqlConnection
Dim command As SqlCommand

…

command = connection.CreateCommand
command.CommandText = query

Dim reader = command.ExecuteReader()
Dim result = New List(Of List(Of String))

For i As Integer = 0 To reader.FieldCount-1
    result.Add(New List(Of String))
Next

While reader.Read()
    For i As Integer = 0 To reader.FieldCount-1
        result(i).Add(reader(i))
    Next
End While

reader.Close()
connection.Close()
…
Response.Write(ser.Serialize(result))

Answer 1

Edit: Since the OP added a code snippet, the issue is the conversion of all result columns to a string.

You'll want all of the columns to be boxed to an object so they retain their underlying data type. Then, the serializer will be able to work appropriately on the type. DateTime objects will then look like my sample below.

To do this, change:

Dim result = New List(Of List(Of String))

to this:

Dim result = New List(Of List(Of Object))

and change this:

result.Add(New List(Of String))

to this:

result.Add(New List(Of Object))

---

Somewhere along the way you must be either implicitly or explicitly calling ToString() on the elements in your array before or during the JSON serialization.

When you execute your reader, it will contain the columns as a DateTime, not a string. When ToString() is called on a DateTime, it will return a string in the current culture format. (en-us looks like this "3/25/2017 11:10:05 AM")

using (var con = new SqlConnection("ConnectionStringHere"))
using (var cmd = new SqlCommand("SELECT SYSDATETIME(), GETDATE();", con))
{
    con.Open();
    var reader = cmd.ExecuteReader();

    var columns = new List<object>();
    while (reader.Read())
    {
        columns.Add(reader[0]);
        columns.Add(reader[1]);
    }

    // The columns list contains SYSDATETIME and GETDATE output
    columns.Dump();

    // The data type of both columns is System.DateTime
    columns[0].GetType().Dump();
    columns[1].GetType().Dump();

    // By default, the JavaScriptSerializer will convert DateTime
    // to the number of ticks, not a pretty string representation
    // e.g. "\/Date(1490454072159)\/"
    // https://msdn.microsoft.com/en-us/library/system.web.script.serialization.javascriptserializer(v=vs.110).aspx
    var serializer = new JavaScriptSerializer();
    serializer.Serialize(columns[0]).Dump();

    reader.Close();
}

Without seeing your code for how you're building your array and passing it to the serializer, I can't know for sure where you're going wrong. But if you maintain the data types and don't treat every result column as a string, the serializer should give you the full precision.

Answer 2

There might be a way to figure out how to do this, but the value is being passed through several different levels of the software stack. That can make any solution tricky.

Perhaps you can just convince the users that if they want a particular format, then use convert():

SELECT SYSDATETIME(), CONVERT(varchar(255), GETDATE(), 121)

In this case 121 does what the asker wants.

There is pretty much how every other tool works that works with databases. Note that this is a minor problem. Toad connected to Oracle, for instance, only shows the date component of a date data type -- even though date in Oracle has a time component.