CODEWITHSUNDEEP
c#sqlsql-serverado.net
Bryan
Bryan

Reputation: 2791

Datetime has too many decimal places

I have the following code:

SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
// set properties on  builder (omitted for brevity)

using (SqlConnection connection = new SqlConnection(builder.ToString()))
{
    connection.Open();
    using (SqlCommand command = connection.CreateCommand())
    {
        command.CommandType = System.Data.CommandType.Text;
        command.CommandText = "SELECT * FROM Employee WHERE WhenHire < @HireDate";
        SqlParameter hireDateParameter = new SqlParameter("@HireDate", DateTime.Now);
        command.Parameters.Add(hireDateParameter);
        using (SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                // do stuff
            }
        }
    }
}

Which shows up in SQL Profiler as:

exec sp_executesql N'SELECT * FROM Employee WHERE WhenHire < @HireDate',
                   N'@HireDate datetime',
                   @HireDate='2010-12-06 11:43:23.573'

How is the precision of the datetime parameter determined? I've seen this have 7 digits after the decimal. 

exec sp_executesql N'SELECT * FROM Employee WHERE WhenHire < @HireDate',
               N'@HireDate datetime',
               @HireDate='2010-12-06 11:43:23.5733125'

In that case, the statement fails to execute with this error:

Msg 8114, Level 16, State 1, Line 1 Error converting data type varchar to datetime.

Upvotes: 3

Views: 3471

Answers (5)

Eric Z Beard
Eric Z Beard

Reputation: 38426

Looks like this is a bug in the 2008 Profiler:

http://blogs.msdn.com/b/sqlserverfaq/archive/2009/08/13/sql-server-2008-profiler-pads-the-datetime-datatype-with-0s-when-tracing-for-a-ado-net-application.aspx

Upvotes: 3

Bryan
Bryan

Reputation: 2791

It looks like it was something strange with the client machine. Not sure exactly what it was, but when we moved to another machine, it no longer added that extra precision. Thanks for all the ideas.

Upvotes: 0

marc_s
marc_s

Reputation: 754868

I would recommend making sure you use a SqlParameter with a DateTime db type - try this code:

SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
// set properties on  builder (omitted for brevity)

using (SqlConnection connection = new SqlConnection(builder.ToString()))
using (SqlCommand command = new SqlCommand(connection))
{
    command.CommandType = System.Data.CommandType.Text;
    command.CommandText = "SELECT * FROM Employee WHERE WhenHire < @HireDate";

    // make sure to have a SqlDbType.DateTime parameter!
    SqlParameter hireDateParameter = new SqlParameter("@HireDate", SqlDbType.DateTime);
    hireDateParameter.Value = DateTime.Now;

    command.Parameters.Add(hireDateParameter);

    // don't open the connection too early - this is early enough here!
    connection.Open();

    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
           // do stuff
        }
    }
}

Upvotes: 3

rswafford
rswafford

Reputation: 374

Per Microsoft (http://msdn.microsoft.com/en-us/library/ms187819.aspx), the T-SQL DateTime type has an accuracy of .000, .003, or .007 milliseconds. Anything with more precision than that could cause that error.

Unless you really need accuracy down to the millisecond, I would give DateTime.Now a format string -- something like "yyyy-MM-dd HH:mm:ss" -- which would give you:

exec sp_executesql N'SELECT * FROM Employee WHERE WhenHire < @HireDate',
                   N'@HireDate datetime',
                   @HireDate='2010-12-06 11:43:23'

The .NET Framework is able to report on millisecond intervals down to seven decimal points, so if your source data is coming from DateTime.Now, you could potentially get up to seven decimal points passed in.

Upvotes: 3

Thomas Langston
Thomas Langston

Reputation: 3735

http://msdn.microsoft.com/en-us/library/ms186724.aspx

Also from microsoft there are multiple date and time data types. One is datetime2 which has increased accuracy and user-defined fractional second precision.

They give an example datetime2 with 7 digits of sub second precision.

Upvotes: 1

Related Questions