Standards for databases, dates and null value handling

[AAJ]

Hi

Does anyone know of any good publically available set of standards for
managing dates when dealing with a database server (in my case SQL Server
2000 and c# VS2005).

At the moment, if I create a record in the database for say a job, the
invoicedOn date field will be left null within the data base, not being
assigned untill its actually invoiced.

I can't figure out how to manipulate these date nulls within c#, for
instance displaying a list jobs with their invoiced date, should just
display formatted dates where they exist and be blank if not. This doesn't
seem to work for null dates when formatting is set on the datagrid. Same
goes if I try set a c# datetime field to null. What happens if I need to
remove a date, I can do this on the server, but I cant figure how to do this
in c#

A temporary measure has been to use isnull() in the server and choose a
really obviously incorrect date, but obviously this is a poor way of
handling things..

So what I'm looking for is a 'good practice' document of how to interact
between client and server with regard to date handling and null dates.

thanks

Andy

[Deepak Khanal]

I'd be interested if there's a published standard on this, but in the
1.* days, I used to compare the DateTime variable with the .NET-defined
constant DateTime.MinValue.

All unassigned DateTime variables inside a class have DateTime.MinValue
as their default value. You cannot set a DateTime variable to null.

But with C# 2.0, you can declare "value" types (including DateTime), to
contain nulls. Here's the difference:

DateTime dt1 = null; //won't work
DateTime? dt2 = null; //will work

Hope it helps...
AAJ wrote:

Hi

Does anyone know of any good publically available set of standards for
managing dates when dealing with a database server (in my case SQL Server
2000 and c# VS2005).

At the moment, if I create a record in the database for say a job, the
invoicedOn date field will be left null within the data base, not being
assigned untill its actually invoiced.

I can't figure out how to manipulate these date nulls within c#, for
instance displaying a list jobs with their invoiced date, should just
display formatted dates where they exist and be blank if not. This doesn't
seem to work for null dates when formatting is set on the datagrid. Same
goes if I try set a c# datetime field to null. What happens if I need to
remove a date, I can do this on the server, but I cant figure how to do this
in c#

A temporary measure has been to use isnull() in the server and choose a
really obviously incorrect date, but obviously this is a poor way of
handling things..

So what I'm looking for is a 'good practice' document of how to interact
between client and server with regard to date handling and null dates.

thanks

Andy

[sloan]

You might consider
http://www.lhotka.net/cslanet/download10.aspx

SmartDate is the object name.

"AAJ" <a.a.comwrote in message
news:u5**************@TK2MSFTNGP05.phx.gbl...

Hi

Does anyone know of any good publically available set of standards for
managing dates when dealing with a database server (in my case SQL Server
2000 and c# VS2005).

At the moment, if I create a record in the database for say a job, the
invoicedOn date field will be left null within the data base, not being
assigned untill its actually invoiced.

I can't figure out how to manipulate these date nulls within c#, for
instance displaying a list jobs with their invoiced date, should just
display formatted dates where they exist and be blank if not. This doesn't
seem to work for null dates when formatting is set on the datagrid. Same
goes if I try set a c# datetime field to null. What happens if I need to
remove a date, I can do this on the server, but I cant figure how to do

this

in c#

A temporary measure has been to use isnull() in the server and choose a
really obviously incorrect date, but obviously this is a poor way of
handling things..

So what I'm looking for is a 'good practice' document of how to interact
between client and server with regard to date handling and null dates.

thanks

Andy

[AAJ]

Thanks for the replies guys

its one of those things that I've been using work arounds for a while now,
and I think its time to standardise all future work.

Thanks for the info and references, and if any one else has more ideas or
want to share examples of their work feel free 8-)

thanks again

Andy
"AAJ" <a.a.comwrote in message
news:u5**************@TK2MSFTNGP05.phx.gbl...

Hi

Does anyone know of any good publically available set of standards for
managing dates when dealing with a database server (in my case SQL Server
2000 and c# VS2005).

At the moment, if I create a record in the database for say a job, the
invoicedOn date field will be left null within the data base, not being
assigned untill its actually invoiced.

I can't figure out how to manipulate these date nulls within c#, for
instance displaying a list jobs with their invoiced date, should just
display formatted dates where they exist and be blank if not. This doesn't
seem to work for null dates when formatting is set on the datagrid. Same
goes if I try set a c# datetime field to null. What happens if I need to
remove a date, I can do this on the server, but I cant figure how to do
this in c#

A temporary measure has been to use isnull() in the server and choose a
really obviously incorrect date, but obviously this is a poor way of
handling things..

So what I'm looking for is a 'good practice' document of how to interact
between client and server with regard to date handling and null dates.

thanks

Andy

[AAJ]

Hi Deepak

based on what you said, the following allows me to reset dates to null in
the database via a stored procedure

if (m_tbl_invoice_date == DateTime.MinValue)
{
Adapter.PRTestDate(m_PK, null);
}
else
{
Adapter.PRTestDate(m_PK, m_tbl_invoice_date);
}
if anyone else reads the post, I hope it helps them

thanks again

Andy
"Deepak Khanal" <dk*****@gmail.comwrote in message
news:11**********************@e3g2000cwe.googlegro ups.com...

I'd be interested if there's a published standard on this, but in the
1.* days, I used to compare the DateTime variable with the .NET-defined
constant DateTime.MinValue.

All unassigned DateTime variables inside a class have DateTime.MinValue
as their default value. You cannot set a DateTime variable to null.

But with C# 2.0, you can declare "value" types (including DateTime), to
contain nulls. Here's the difference:

DateTime dt1 = null; //won't work
DateTime? dt2 = null; //will work

Hope it helps...
AAJ wrote:

>Hi

Does anyone know of any good publically available set of standards for
managing dates when dealing with a database server (in my case SQL Server
2000 and c# VS2005).

At the moment, if I create a record in the database for say a job, the
invoicedOn date field will be left null within the data base, not being
assigned untill its actually invoiced.

I can't figure out how to manipulate these date nulls within c#, for
instance displaying a list jobs with their invoiced date, should just
display formatted dates where they exist and be blank if not. This
doesn't
seem to work for null dates when formatting is set on the datagrid. Same
goes if I try set a c# datetime field to null. What happens if I need to
remove a date, I can do this on the server, but I cant figure how to do
this
in c#

A temporary measure has been to use isnull() in the server and choose a
really obviously incorrect date, but obviously this is a poor way of
handling things..

So what I'm looking for is a 'good practice' document of how to interact
between client and server with regard to date handling and null dates.

thanks

Andy

[Deepak Khanal]

Cool. No problem! -DK

AAJ wrote:

Hi Deepak

based on what you said, the following allows me to reset dates to null in
the database via a stored procedure

if (m_tbl_invoice_date == DateTime.MinValue)
{
Adapter.PRTestDate(m_PK, null);
}
else
{
Adapter.PRTestDate(m_PK, m_tbl_invoice_date);
}
if anyone else reads the post, I hope it helps them

thanks again

Andy
"Deepak Khanal" <dk*****@gmail.comwrote in message
news:11**********************@e3g2000cwe.googlegro ups.com...

I'd be interested if there's a published standard on this, but in the
1.* days, I used to compare the DateTime variable with the .NET-defined
constant DateTime.MinValue.

All unassigned DateTime variables inside a class have DateTime.MinValue
as their default value. You cannot set a DateTime variable to null.

But with C# 2.0, you can declare "value" types (including DateTime), to
contain nulls. Here's the difference:

DateTime dt1 = null; //won't work
DateTime? dt2 = null; //will work

Hope it helps...
AAJ wrote:

Hi

Does anyone know of any good publically available set of standards for
managing dates when dealing with a database server (in my case SQL Server
2000 and c# VS2005).

At the moment, if I create a record in the database for say a job, the
invoicedOn date field will be left null within the data base, not being
assigned untill its actually invoiced.

I can't figure out how to manipulate these date nulls within c#, for
instance displaying a list jobs with their invoiced date, should just
display formatted dates where they exist and be blank if not. This
doesn't
seem to work for null dates when formatting is set on the datagrid. Same
goes if I try set a c# datetime field to null. What happens if I need to
remove a date, I can do this on the server, but I cant figure how to do
this
in c#

A temporary measure has been to use isnull() in the server and choose a
really obviously incorrect date, but obviously this is a poor way of
handling things..

So what I'm looking for is a 'good practice' document of how to interact
between client and server with regard to date handling and null dates.

thanks

Andy