SQL Server 2008 stored procedure with DateTime2
I have a small question about stored procedures and the DateTime2
datatype in SQL Server 2008.
I got several stored procedures using the DateTime2
datatype and I want to insert the date 0001.01.01 00:00:00
but this won't work with VB.net and I cannot find the reason why.
I use this code fragment:
Dim sqlStatement As New SqlClient.SqlCommand
Dim sqlTransaction As SqlClient.SqlTransaction
sqlStatement.CommandType = CommandType.StoredProcedure
sqlStatement.CommandText = "SCHEMA.spInsertDate"
sqlStatement.Parameters.AddWithValue("@Date", "0001.01.01 00:00:00")
sqlStatement.ExecuteNonQuery()
to call the stored procedures from my program (the stored procedure is a simple INSERT
statement nothing else and works fine with actual dates). But when I enter the date 0001.01.01 00:00:00
it always comes up with the error that I cannot insert dates before '01.01.1753' when I want to execute the stored procedure.
Now I already know that the DateTime2
datatype in SQL Server should support this.
So my question is it possible that this is a driver problem and updating the SQLClient would solve this, or is this a general problem and I can finally stop s开发者_如何学Goearching and just use 1753.01.01.
Thanks Lim
If you don't specify anything for your SQL parameter and use the .AddWithValue()
method, I believe ADO.NET will default to use SqlDbType.DateTime
which has this limitation in its range (no dates before 1/1/1753).
You need to explicitly define the datatype of your parameter with this call:
SqlParameter dateTime2Param = sqlStatement.Parameters.Add("@Date", SqlDbType.DateTime2);
dateTime2Param.Value = "0001.01.01 00:00:00";
Then it should work, I believe. The SqlDbType
enumeration had this DateTime2
type added in .NET 3.5
精彩评论