2

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 searching and just use 1753.01.01.

Thanks Lim

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Lim
  • 135
  • 1
  • 2
  • 8

1 Answers1

6

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • @Lim: yes, the `.AddWithValue()` is convenient - but a bit dangerous at times, for its not always obvious right from the start what `SqlDbType` will be picked for your value provided. I prefer to always **explicitly** tell the parameter what `SqlDbType` it is - less possibility of a (nasty) surprise ... – marc_s Aug 19 '11 at 09:09
  • I see what you mean it's the first time i actually ran into a problem with the .AddWithValue() Function. I will keep that in mind for future programs :) Thank you very much for clearing that up :) – Lim Aug 19 '11 at 09:15