I just hate having nulls in my DateTime columns. Having them always mean you also have to handle them in some way in your app.
The most common way is something like this:
public DateTime SomeDate
{
get
{
if (dr["SomeDate"] == DBNull.Value)
return DateTime.MinValue;
else
return (DateTime)dr["SomeDate"];
}
set
{
if (value == DateTime.MinValue)
dr["SomeDate"] = DBNull.Value;
else
dr["SomeDate"] = value;
}
}
Of course you could write improvements in form of attributes or something similar to reduce the repetetive coding but in the end this had to be implemented.
Yes, having nulls saves space, but disk space is cheap :)
That's why i use the default value for my DateTime columns it's max value which is '99991231 23:59:59:997'. you can use min value if you like it more :)
i use this function for my default values
CREATE FUNCTION dbo.DateTimeMaxValue()
RETURNS DATETIME
AS
BEGIN
RETURN CONVERT(DATETIME, '99991231 23:59:59:997')
END
GO
and what would MaxValue be without MinValue
CREATE FUNCTION dbo.DateTimeMinValue()
RETURNS DATETIME
AS
BEGIN
RETURN CONVERT(DATETIME, '17530101')
END
GO
i don't know why sql server doesn't already have these functions. If it, by any chance, does have them then i'd love to know about them.
However this again presents some problems since SQL datetime and .Net datetime have different ranges.
| |
Min Date |
Max Date |
| .Net |
0001-01-01 00:00:00.000 |
9999-12-31 23:59:59.999 |
| SQL Server |
1753-01-01 00:00:00.000 |
9999-12-31 23:59:59.997 |
If you have SQL Server 2005 and would like to extend your DateTime range to match .Net's read my post
here.
So we see that you can't simply do
if (.net datetime maxvalue == sql server datetime maxvalue)
BUT...
In .Net 2.0 we got the System.Data.SqlTypes namespace which gives us native SQL datatypes in .net.
So we also get SqlDateTime, which has a MaxDate and MinDate properties.
and
SqlDateTime.MaxValue = 9999-12-31 23:59:59.997
SqlDateTime.MinValue = 1753-01-01 00:00:00.000
So you can easily find which dates have the max/min value and handle those anyway you like.
At least something is a bit simplified.
Now i'd just love to be able to do dr["SomeDate"] = SqlDateTime.Null but despite SqlDateTime.Null exists you can't assign it like that.
You still have to use DbNull.Value.
