SqlDateTime.MinValue, SqlDateTime.MaxValue: WATCH OUT!

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

Because DotNet (inexplicably) can't directly cast SqlDateTime to DateTime
using either CDate or Convert, you have to use ToString first.

I use this function to validate a SQL date:

Public Function IsValidSQLDate(ByVal d As DateTime) As Boolean
Return CDate(SqlDateTime.MinValue.ToString) <= d _
AndAlso CDate(SqlDateTime.MaxValue.ToString) >= d
End Function

On normal, sane machines:

SqlDateTime.MinValue.ToString = '1/1/1753 12:00:00AM'
SqlDateTime.MaxValue.ToString = '12/31/9999 11:59:59PM'.

But on Windows NT machines (even service packed and updated as much as
humanly possible):

SqlDateTime.MinValue.ToString = '1/1/53 12:00:00AM'
SqlDateTime.MaxValue.ToString = '12/31/99 11:59:59PM'

As such, on Windows NT machines the IsValidSQLDate function above fails to
recognize any date not between 1953 and 1999, thereby rendering the values
MS so graciously provides for SqlDateTime.MinValue and SqlDateTime.MaxValue
utterly useless.

Bob
 
Bob,
Because DotNet (inexplicably) can't directly cast SqlDateTime to DateTime
using either CDate or Convert, you have to use ToString first.
Click to expand...
Actually .NET can convert them without using ToString first! Specifically
current versions of C# can & VB.NET 2005 will be able to.

SqlDateTime has conversion operators defined between DateTime & SqlDateTime:

http://msdn.microsoft.com/library/d...ClassSqlDateTimeToDateTimeConversionTopic.asp

http://msdn.microsoft.com/library/d...ClassDateTimeToSqlDateTimeConversionTopic.asp

VB.NET 2002 & VB.NET 2003 is unable to "implicitly" use these operators as
they do not support Operator Overloading. Current versions of C# are able to
use Operator Overloading. While VB.NET 2005 (aka Whidbey, due out later in
2005) will be able to use & define Operator Overloading,

http://msdn2.microsoft.com/library/hddt295a.aspx

Including conversion operators:

http://msdn2.microsoft.com/library/yf7b9sy7.aspx


To use these operators in VB.NET 2002 & VB.NET 2003 you need to "explicitly"
call the specially named overloaded operator routine. Something like:

Dim dt As DateTime
Dim sdt As SqlDateTime

' VB.NET 2002 & VB.NET 2003 syntax
dt = SqlDateTime.op_Explicit(sdt)
sdt = SqlDateTime.op_Implicit(dt)

In VB.NET 2005 the compiler will call the correct routine based on the CType
(convert type) operator.

' VB.NET 2005 syntax
dt = CType(sdt, DateTime)
sdt = CType(dt, SqlDateTime)

VB.NET 2005 will also implicitly call the Widening Operator (op_Implicit)
sdt = dt

Hope this helps
Jay
 
Cor Ligthert said:
I am curious, why would you use that instead of directly datetime values?
Click to expand...

'SqlDateTime' can be used in a parameterzied SQL command, for example.
Unlike Date' 'SqlDateTime' can hold a null value.
 
Bob,
I should have included a sample using the "overloaded operators" based on
your code.

Public Function IsValidSQLDate(ByVal d As DateTime) As Boolean
Dim minValue As DateTime =
SqlDateTime.op_Explicit(SqlDateTime.MinValue)
Dim maxValue As DateTime =
SqlDateTime.op_Explicit(SqlDateTime.MaxValue)
Return minValue <= d AndAlso d <= maxValue
End Function

I would consider making minValue & maxValue as readonly shared fields in the
class, as they will not change during the live of your program.

Also the "overloaded operators" are normally hidden to VB.NET 2002 & VB.NET
2003, you can use "Tools - Options - Text Editor - Basic - General - Hide
advanced members" to hide or show advanced members in the text editor.

Hope this helps
Jay
 
Jay B. Harlow said:
To use these operators in VB.NET 2002 & VB.NET 2003 you need to "explicitly"
call the specially named overloaded operator routine. Something like:

Dim dt As DateTime
Dim sdt As SqlDateTime

' VB.NET 2002 & VB.NET 2003 syntax
dt = SqlDateTime.op_Explicit(sdt)
sdt = SqlDateTime.op_Implicit(dt)
Click to expand...

*sigh*

Well I post here to obtain knowledge at the price of feeling stupid, on
occasion.

Thanks, for the help. :)

Bob
 
Jay B. Harlow said:
Also the "overloaded operators" are normally hidden to VB.NET 2002 & VB.NET
2003, you can use "Tools - Options - Text Editor - Basic - General - Hide
advanced members" to hide or show advanced members in the text editor.

Hope this helps
Jay
Click to expand...

I look darkly on a few decisions MS made about VB. That feature's one of
them...

Bob
 
Herfried,
'SqlDateTime' can be used in a parameterzied SQL command, for example.
Unlike Date' 'SqlDateTime' can hold a null value.
Click to expand...
I see and therefore
dim MinDate as DateTime = New DateTime(1753,1,1,12,0,0)
dim MaxDate as DateTime = New DateTime(9999,12,31,23.59,59)

Should not be used.

Thanks for enlighten me with that. However, somewhere have I the idea that
your answer does not fit my question.

:-)

Cor
 
Cor,
I see and therefore
dim MinDate as DateTime = New DateTime(1753,1,1,12,0,0)
dim MaxDate as DateTime = New DateTime(9999,12,31,23.59,59)

Should not be used.
Click to expand...
Correct as your MinDate doesn't match SqlDateTime.MinValue its off by 12
hours! Your MaxDate doesn't match SqlDateTime.MaxValue as its missing
fractions of seconds.

To see how far off your "duplicate" constants are try:

Dim MinDate As DateTime = New DateTime(1753, 1, 1, 12, 0, 0)
Dim MaxDate As DateTime = New DateTime(9999, 12, 31, 23, 59, 59)

Debug.WriteLine(MinDate.Subtract(SqlDateTime.op_Explicit(SqlDateTime.MinValue)),
"minDateDiff")
Debug.WriteLine(MaxDate.Subtract(SqlDateTime.op_Explicit(SqlDateTime.MaxValue)),
"maxDateDiff")

More seriously SqlDateTime.MinValue & SqlDateTime.MaxValue are readonly
fields not Constants. Being fields mean their value may change, & any
assembly using those fields will automatically see the change without
needing to be recompiled. Converting SqlDateTime.MinValue itself will ensure
your program will always use the true MinValue rather then some arbitrary
value that currently matches! Hence my suggestion of using
SqlDateTime.op_Explicit to convert the SqlDateTime to DateTime values.

Lastly you are duplicating "constants", which I hope you will agree, makes
maintenance harder. For when the "constant" changes so does every place its
duplicated...

Hope this helps
Jay


If you hard code your program to DateTime(1753,1,1,12,0,0)
 
Jay,

I am glad you wrote that, I told more times that I become always confused by
"am" and "pm". (As you know do we use in the EU a 24 hours clock)

I thought let me do it right this time it should be 12 and again error.

I have a program where I add for dates older than 01-01-1800 (it is just a
genealogic own family program, probably you saw it once on internet) 2000
and visa versa. It needs not to be precise you know when you needs them
older than those dates and do it consequent.

Although I thank you for your message because I now will probably never
forget it about the SQLdateTime anymore. It is a good kind of reminder.

We both have seen that dates before that 1753 because of the introduction of
the Georgian calandar at last at september 1752 in England, those dates
before 1753 can badly be calculated when you don't know exactly what
calander was used..
Lastly you are duplicating "constants", which I hope you will agree, makes
maintenance harder. For when the "constant" changes so does every place its
duplicated
Click to expand...

Although that is probably not what you mean, does it make sense for me.
These values are inbuild. When we make them ourselves we should set them
global/shared, and that is something I try to avoid as much as possible. I
did not know them, so probably I will use them next time.

:-)

Cor
 
Back
Top