SQLDateTime Overflow inserting DateTime that is Nothing

[Charles Law]

I am using VB (although the language is presumably academic) to insert
records into a SQL Server 2005 database. The database has a datetime column
that is nullable.

Inserting dates normally is fine, but when I have a datetime variable in VB
that returns Nothing, I get an overflow exception from the database.

What is the correct way to deal with datetime types that evaluate to
Nothing?

I realise that I could test the variable and insert something like
System.DBNull.Value when the variable is Nothing, but that seems like a
kludge. What is the designed Microsoft approach to this problem?

TIA

Charles

 

[Uri Dimant]

Charles
Insert a value as string value with format YYYYMMDD

 

[Charles Law]

Hi Uri

I haven't tried this, but as I understand the problem it is that the
datetime variable is out of range for the database column.

A datetime variable in VB/C# that equates to Nothing is represented by the
string 1/1/01 12:00 A.M. If I try to insert this using the string format you
suggest I believe I will still get the same exception.

What I think I need is a way to insert the datetime variable in such a way
that when it is Nothing the database receives a Null value that it
understands.

Charles

 

[Armin Zingler]

I am using VB (although the language is presumably academic) to
insert records into a SQL Server 2005 database. The database has a
datetime column that is nullable.

Inserting dates normally is fine, but when I have a datetime
variable in VB that returns Nothing,

How can it be Nothing? DateTime is a value type that can never be
Nothing. Is it Nullable(Of DateTime)?

(unfortunatelly the type is named 'Nullable'; don't mix it with DBNull.
'Nothing' is clearer in VB.Net)

I get an overflow exception
from the database.

What is the correct way to deal with datetime types that evaluate to
Nothing?

Unfortunatelly, VB.Net allows

ValueTypeVar = Nothing

However, this does *not* assign Nothing, in the sense of 'no reference'.
Instead, it assigns the type's default value, which is DateTime.MinValue
(January 1, 0001). This value is out of the data type range in SQL
server (years 1753-9999; smalldatetime=1900 - 6th Jan 2079). Therfore
the error.

So, never use 'Nothing' with value types. It's only confusing.
Use 'Nothing' only with reference types.

I realise that I could test the variable and insert something like
System.DBNull.Value when the variable is Nothing, but that seems
like a kludge. What is the designed Microsoft approach to this
problem?

I'm not MSFT, but... It depends on your (in-memory) data storage. If
you use a DataAdapter and a typed DataSet, it should do the work for
you. Internally it's stored 'As Object' which allows DateTime as well as
DBNull.Value.
Otherwise, use Nullable(Of DateTime) and translate it to DBNull.Value if
HasValue=False.



Armin

 

[Uri Dimant]

Charlse
For the 'safety' as I saud, please convert date to YYYYMMDD format to be
inserted

 

[Dan Guzman]

I realise that I could test the variable and insert something like

System.DBNull.Value when the variable is Nothing, but that seems like a
kludge. What is the designed Microsoft approach to this problem?

Unfortunately, you'll need to specify System.DBNull.Value when you want a
database NULL. A null reference (Nothing) is not the same thing as a
System.DBNull.Value value. The underlying issue is that .NET data types do
not necessarily map directly to SqlDbTypes. For example, System.DataTime
has a greater range than a SQL Server datetime. Personally, I use helper
classes to take care the conversion and null values.

I can't say why you are getting an overflow error without seeing your code.
It seems to me that the supplied datetime value supplied is a value outside
of the allowable SQL datetime range rather than Nothing.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

 

[Armin Zingler]

Charlse
For the 'safety' as I saud, please convert date to YYYYMMDD format
to be inserted

He wants to insert 'Null'. How do you expect him to format it to
'YYYYMMDD'?

It's always recommended to use the SqlCommand's Parameters property. If
you use it you don't have to care about the format at all.


Armin

 

[Charles Law]

Hi Armin

How can it be Nothing? DateTime is a value type that can never be
Nothing. Is it Nullable(Of DateTime)?

Because

MyDateTimeVar.Equals(Nothing)

returns True.

Unfortunatelly, VB.Net allows

ValueTypeVar = Nothing

Not just unfortunate, I would say, but simply wrong.

That said, I take your points entirely. They completely explain why I have
the problem.

If you use a DataAdapter and a typed DataSet, it should do the work for
you. Internally it's stored 'As Object' which allows DateTime as well as
DBNull.Value.

I do use a DataAdapter and DataSet, but still get the exception.

Otherwise, use Nullable(Of DateTime) and translate it to DBNull.Value if
HasValue=False.

I'm not quite sure what you mean here. Could you explain a bit further?

Thanks.

Charles

 

[Charles Law]

Hi Dan

Unfortunately, you'll need to specify System.DBNull.Value when you want a
database NULL. A null reference (Nothing) is not the same thing as a
System.DBNull.Value value. The underlying issue is that .NET data types
do not necessarily map directly to SqlDbTypes. For example,
System.DataTime has a greater range than a SQL Server datetime.

From Armin's comments and the nature of the exception, I now understand the
issue here. What I was hoping was that Microsoft would have a built-in way
to handle this scenario. It must be very common to want to put a datetime
variable into a SQL Server database when the variable has a value that
represents an uninitialised datetime.

Armin indicated that it should be handled for me behind the scenes if I use
a DataAdapter and DataSet, but id doesn't seem to be.

I'm sure I'm still doing something wrong, and I'd like to change whatever I
need to so that it works as MS intended, but not if it means using a kludge.

Charles

 

[Uri Dimant]

My bad Armin. I miss read it. I'm not a .NET expert , but I think the OP
needs specify System.DBNull to insert NULL

It's always recommended to use the SqlCommand's Parameters property. If
you use it you don't have to care about the format at all.

It is safety using YYYYMMDD format to deal with dates, does not it base on
Regional Settings/Language if you not specify format

For examlpe the below does not work

SET LANGUAGE british
SELECT CAST('2003-02-28' AS datetime)

 

[Armin Zingler]

Hi Armin


Because

MyDateTimeVar.Equals(Nothing)

returns True.


Not just unfortunate, I would say, but simply wrong.

Well, it's 'by design' and documented this way. Well, bad design IMO
(and IYO). At least there should be an option that makes this a warning
or compile error.

That said, I take your points entirely. They completely explain why
I have the problem.


I do use a DataAdapter and DataSet, but still get the exception.

I meant, if it contains DBNull.Value you don't have to care about it on
your own. But if you assign Nothing, you actually assign 1/1/0001. The
Dataadapter doesn't know that this is out of range.

I'm not quite sure what you mean here. Could you explain a bit
further?

I said it without knowing how you store the value, and I meant:

dim dt as Nullable(of datetime)

'...

if dt.HasValue then
cmd.parameters(...).Value = dt
else
cmd.parameters(...).Value = DBNull.Value
end if


But because you are using a DataSet+DataAdapter, this is "oboslete"; but
you get the point.

So, if you work with the DataSet, assign DBNull.Value if you need to.
The DataAdapter will do the required insert/update of NULL into the
database.


Armin

 

[Armin Zingler]

My bad Armin. I miss read it. I'm not a .NET expert , but I think
the OP needs specify System.DBNull to insert NULL


It is safety using YYYYMMDD format to deal with dates, does not it
base on Regional Settings/Language if you not specify format

For examlpe the below does not work

SET LANGUAGE british
SELECT CAST('2003-02-28' AS datetime)

Ok, I see. Didn't see this one also reached a none .Net group.

The advantage of a Parameter object is that you don't have to care about
the format at all. You simply assign the value - a valid date or
DBNull.Value (this is a kind of constant representing NULL in the .Net
world) - and the rest is done for you, independent from regional/culture
settings. And it's safe concerning SQL injection (discussed multiple
times in this (VB.Net) group and others).


Armin

 

[Dan Guzman]

Armin indicated that it should be handled for me behind the scenes if I

use a DataAdapter and DataSet, but id doesn't seem to be.

Armin specifically mentioned a typed DataSet. Is that what you are using?
I haven't used use a typed dataset since before .NET 2.0 but I would think
you would need to avoid assigning the datetime value to the DataRow to
retain a NULL value of the newly created row in the dataset. There really
is no such thing as a null (Nothing) DataTime reference so assigning a any
value except for a DbNull /Nullable will result in a non-NULL value.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

 

[Dan Guzman]

Uri, the string datetime format only applies in the T_SQL world. Values can
be passed from application code natively as described by Armin.

See
http://weblogs.sqlteam.com/dang/archive/2008/02/18/Why-Parameters-are-a-Best-Practice.aspx
for my spin on the subject.


--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

 

[Uri Dimant]

Armin

The advantage of a Parameter object is that you don't have to care about
the format at all. You simply assign the value - a valid date or
DBNull.Value (this is a kind of constant representing NULL in the .Net
world) - and the rest is done for you, independent from regional/culture
settings. And it's safe concerning SQL injection (discussed multiple times
in this (VB.Net) group and others).

I fully agree with you, it is true especially when it comes to deal SQL
injection were discussed at this forum many times as well ))

 

[Uri Dimant]

Dan

Uri, the string datetime format only applies in the T_SQL world.

Yes , I have already realized it , just long time did touch on .NET
)))))

I added your blog to my favorite links and hope you will keep on writing
great articles in the future )

 

[Charles Law]

if dt.HasValue then

cmd.parameters(...).Value = dt
else
cmd.parameters(...).Value = DBNull.Value
end if
and

So, if you work with the DataSet, assign DBNull.Value if you need to.
The DataAdapter will do the required insert/update of NULL into the
database.

I understand the technique then, but these are both what I mean by a kludge.
Given that one of the most common things one might want to do is put data in
a database, why do I need to start testing the values of a built-in data
type and make a special case out of some values? If this is the case for
datetime types then presumably it affects other types, in different ways?

It means that everywhere I want to store a datetime variable in a column I
have to test it first. This just seems like it is designed to be awkward.

Am I missing something? Isn't there a more natural way to do this?

Charles

 

[Armin Zingler]

I understand the technique then, but these are both what I mean by a
kludge. Given that one of the most common things one might want to
do is put data in a database, why do I need to start testing the
values of a built-in data type and make a special case out of some
values?

Because it _is_ a special case. Though,... (see below)

If this is the case for datetime types then presumably it
affects other types, in different ways?

It means that everywhere I want to store a datetime variable in a
column I have to test it first. This just seems like it is designed
to be awkward.

Am I missing something? Isn't there a more natural way to do this?

Where is the problem if you use a typed dataset?

I have to add to my previous post: Assigning DBNull.Value only works
with DataSets that are not typed. With typed Datasets, you have to call
MyDS.SetXYZColumNull to set it to NULL and MyDS.IsXYZColumnNull to check
for NULL. Though, this doesn't affect the way the DataAdapter updates
the table.

Anyways, I don't see any usage for 'Nothing' in this case. If you don't
use it, you won't have a problem.


Armin

 

[Charles Law]

Armin specifically mentioned a typed DataSet. Is that what you are using?

Er, no. I remember now looking at typed datasets a while ago, and being
horrified at the amount of code generated for even a small table. It put me
off.

Perhaps I should revisit :-(

Charles

 

[Charles Law]

I'm struggling to come to terms with the fact that what seems to be a
straight forward problem, does not have [what I consider to be] a straight
forward solution.

As I replied to Dan, I recall looking at typed DataSets some time ago, but
was very concerned by the amount of code generated in the background, that
could perhaps be rendered useless if the schema of my table changed even
slightly; or have I got that bit wrong as well?

I think I need to lie down in a darkened room for a bit and get my head
round this ;-)

Charles