SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM

  • Thread starter Thread starter mlafarlett
  • Start date Start date
M

mlafarlett

Alright..I've found lots of info on this error and most say assign
System.DBNull.Value, System.Convert.DBNull, or
System.Data.SqlTypes.SqlDateTime.Null to my stored proc param and the
problem will be solved...NOT. I suspect all of these are the same
thing, however, none solve my problem. What I have is vb.net code
trying to put a null in a sql2000 datatime field. To further confuse
things, it works on my PC and not from another. I checked the DSN
being used on each machine were the same and they were. I confirmed
the SQL data type is a 'datetime' and that it is NULLable.

Code:

Dim prmBatchTsUpd As SqlParameter = new SqlParameter
prmBatchTsUpd.ParameterName = "@batch_ts"
prmBatchTsUpd.SqlDbType = SqlDbType.DateTime
prmBatchTsUpd.SourceColumn = "batch_ts"
sqlUpdateProCmd.Parameters.Add(prmBatchTsUpd)

prmBatchTsUpd.Value = System.Data.SqlTypes.SqlDateTime.Null

sqlUpdateProCmd.ExecuteNonQuery()

Stored Procedure Definition:
CREATE Procedure dbo.si_sp_XFERSERVICE_update_xfer_profile
(
@program_name varchar(40),
@active char(1),
@batch_ts datetime
)
as

update css$steph.dbo.xfer_profile
set active = @active,
batch_ts = @batch_ts
where program_name = @program_name

Help please?
 
If the same code is working on one machine and not another, then I am
guessing the other machine is going to a different database. That, or it is
running an older copy of the code or something.
 
I do not completely understand your question.

But 32 december 2006 is not allowed, you should than use 1 january 2007.

Setting a VB dbnull.value in a SQL datetime field should not be a problem
especially not as you use parameters.

Cor
 
I've confirmed, twice, that I'm running the same code and as for
pointing at a different DB, no way, the code would not even run if that
were true. My tables / stored procs are vastly different, meaning, I'm
referencing columns that do not even exist in prior environments and an
error stating so would be occurring. The 'subject' of this thread is
what's being returned as the error when attempting to pass the null.
I'm not sure where you're getting the 32 december thing...sorry if my
question is not clear.
 
Ok..have narrowed it down some but still now solved...its complaing
about this statement.

prmBatchTsUpd.Value = System.Data.SqlTypes.SqlDateTime.Null

keeping in mind i've also tried :
prmBatchTsUpd.Value = System.DBNull.Value
prmBatchTsUpd.Value = System.Convert.DBNull
 
Why don't you fire up SQL Profiler and see what is actually getting
submitted to the database.

I had this error once, but it turned out I was trying to update a
datetime column in the db with a datetime variable that had the value
DateTime.MinValue which is invalid for SQL Server.

Good luck

Chris
 
Back
Top