Are DateTime datatypes worth the aggravation?

[Tony Van]

They're driving me nuts, especially with the Datareader.
There are times when I want to pass a null date on
to a date variable and I have to cast the null as a
date!!!???

I'm wondering if I should just forget it and dates and times
as a string in the database table.

Anyone wrestle with this decision?

[pvdg42]

"Tony Van" <to*********@comcast.netwrote in message
news:Vp******************************@comcast.com. ..

They're driving me nuts, especially with the Datareader. There are times
when I want to pass a null date on
to a date variable and I have to cast the null as a date!!!???

I'm wondering if I should just forget it and dates and times as a string
in the database table.

Anyone wrestle with this decision?

I guess it would depend on what sort of processing you need to do with dates
and times. If all you need to do is store a date and time, and you'll never
need to compare two date-times, or do any other manipulation, then I guess
strings are OK. If you ever did need to calculate an interval between two
date-times, or, say, add a value, such as 30 days to a date-time, etc. you
might be sorry you chose strings.

[Göran Andersson]

If you store dates as a string, you are more or less breaking the first
normal form, as you are storing the date as several separate values in
the same field. That would give you a hint that it might not be the best
idea.

If you store the dates as strings, you can't really use them for much
anything than displaying them, and even then you can run into problems.
What if you one day need to display the dates in a different format?
Then you would have to convert the strings to dates, then convert them
back to strings in a different format. You will look at the mess, and
wonder what the @&#%¤ you were thinking when you stored the dates as
strings...

If you are using framework 2, you can use a nullable date to handle date
values that may be null.

Tony Van wrote:

They're driving me nuts, especially with the Datareader.
There are times when I want to pass a null date on
to a date variable and I have to cast the null as a
date!!!???

I'm wondering if I should just forget it and dates and times
as a string in the database table.

Anyone wrestle with this decision?

[Cor Ligthert [MVP]]

Tony,

As addition to the others, if your program is only build for one culture,
but only one culture than it maybe fit. And with culture I mean culture by
instance the USA culture which fits almost nowhere in the world beside the
USA (even not Canada or Australia). (Microsoft software including dotNet by
instance is MultiCulture)
Cor

"Tony Van" <to*********@comcast.netschreef in bericht
news:Vp******************************@comcast.com. ..

They're driving me nuts, especially with the Datareader. There are times
when I want to pass a null date on
to a date variable and I have to cast the null as a date!!!???

I'm wondering if I should just forget it and dates and times as a string
in the database table.

Anyone wrestle with this decision?

[Tom Leylan]

I'll take a slightly different tack from the other replies. I'll say
"probably" you want to use the datetime but be willing to consider
alternatives if you have special needs.

I'm looking at the SQL Server docs right now and it plainly says:I'm looking
at the SQL Server docs right now and it plainly says: "Date and time data
from January 1, 1753, through December 31, 9999, with an accuracy of 3.33
milliseconds "

It seems clear that if your dates fall outside of that range (earlier only I
would imagine) you'll encounter problems. Similarly if you need more than
just "null" (there is such a concept as multiple null values (they are all
null but for different reasons)) you can't represent them. The alternative
(and a bad one) is to choose particular dates (maybe the highest few) to
represent these special values.

Hope this helps,
Tom

"Tony Van" <to*********@comcast.netwrote in message
news:Vp******************************@comcast.com. ..

They're driving me nuts, especially with the Datareader. There are times
when I want to pass a null date on
to a date variable and I have to cast the null as a date!!!???

I'm wondering if I should just forget it and dates and times as a string
in the database table.

Anyone wrestle with this decision?

[Tony Van]

All true, but a null date in some areas of my DB are
meaningful. I suppose I can make the MinDate serve as a
flag for an empty date. But you know, I'm already casting
all over the place in my code so a cast from string to
datetime seem trivial.

Thanks for the input. as I continue to mull this over.

[Kevin Spencer]

Hi Tony,

Casting a string to datetime may be trivial, but running queries on string
dates is not. If you want to be able to work with your datetime data in the
database itself (filter conditions, sorting, for example), use the datetime
data type.

Think about it. You've already acquainted yourself with the DateTime data
type, and most of the issues that surround it when working with ADO.Net and
Controls. There are some very good reasons for the DateTime data type, some
of which you may not even be aware of (such as TimeZone issues). Let me
point you to some excellent resources to help you get over the hump, and
then you can decide for yourself what to do:

http://msdn2.microsoft.com/en-us/library/ms172138.aspx
http://support.microsoft.com/kb/313513
http://msdn2.microsoft.com/en-us/library/ms228044.aspx
http://www.codeproject.com/cs/miscct...TimePicker.asp
http://www.codeguru.com/csharp/cshar...e.php/c9645__1
http://msdn2.microsoft.com/en-us/library/1t3y8s4s.aspx
http://msdn2.microsoft.com/en-us/library/2cf62fcy.aspx
http://msdn2.microsoft.com/en-us/lib....nullable.aspx

--
HTH,

Kevin Spencer
Microsoft MVP
Short Order Coder
http://unclechutney.blogspot.com

The devil is in the yada yada yada
"Tony Van" <to*********@comcast.netwrote in message
news:mI******************************@comcast.com. ..

All true, but a null date in some areas of my DB are meaningful. I
suppose I can make the MinDate serve as a flag for an empty date. But
you know, I'm already casting all over the place in my code so a cast
from string to datetime seem trivial.

Thanks for the input. as I continue to mull this over.

[Tom Leylan]

To be clear I'm not suggesting that you should or shouldn't use the datetime
datatype.

Re: null values, I was also pointing out that one "null" is often not
enough. A null value is generally interpreted as "never been entered" when
in fact that is only one possibility. It may be unknown or unavailable
which may be considered "we entered a null" rather than "it has a null
because we never entered it". There can be "doesn't apply" type nulls as
well as others. These are fairly easy to represent as coded values but with
datetime you have to choose a real datetime to represent each null subtype.

BTW MinDate is a date that could reasonably represent real data. Given the
range of datetime you can't even store Columbus's voyage to America as a
datetime type. Nothing we tend to store consists of MaxDate values however
and I know of no database that has 12/31/9999 as a date which represents
anything real.

I'll also suggest that everybody casts all over the place in order to
display things properly . An item stored as Boolean doesn't imply we want F
or T as the representation of the value. These are often shown as
True/False, Yes/No or what have you and as such are cast to strings. So I
agree it isn't an issue. Basically see if datetime meets your requirements
much like you check if any other data type meets your requirements. Am I
mistaken in believing that if people need to store dates for the first 17
centuries AD they aren't doing it using datetime?

Keep mulling :-)
"Tony Van" <to*********@comcast.netwrote in message
news:mI******************************@comcast.com. ..

All true, but a null date in some areas of my DB are meaningful. I
suppose I can make the MinDate serve as a flag for an empty date. But
you know, I'm already casting all over the place in my code so a cast
from string to datetime seem trivial.

Thanks for the input. as I continue to mull this over.

[Cor Ligthert [MVP]]

Tom,

Be aware that Columbus voyage to America took place before 1582 the year
most modern western countries from that time started with the Georgian
Calendar. The British Empire (including current USA) started with that in
September 1752 therefore dates during that and before that period are very
insecure measured to our current standards.

Although I am probably one of the most datetime puritans in these newsgroup:
I use string dates in our kind of genealogic families website in a XML file
(dataset). The reason is the one you mentioned and because that I than don't
have to change in the browser. I have the information in a database where I
add 2000 to all dates before 1800 and there it are true datetime variables.

Cor
"Tom Leylan" <ge*@iamtiredofspam.comschreef in bericht
news:%2******************@TK2MSFTNGP05.phx.gbl...

To be clear I'm not suggesting that you should or shouldn't use the
datetime datatype.

Re: null values, I was also pointing out that one "null" is often not
enough. A null value is generally interpreted as "never been entered"
when in fact that is only one possibility. It may be unknown or
unavailable which may be considered "we entered a null" rather than "it
has a null because we never entered it". There can be "doesn't apply"
type nulls as well as others. These are fairly easy to represent as coded
values but with datetime you have to choose a real datetime to represent
each null subtype.

BTW MinDate is a date that could reasonably represent real data. Given
the range of datetime you can't even store Columbus's voyage to America as
a datetime type. Nothing we tend to store consists of MaxDate values
however and I know of no database that has 12/31/9999 as a date which
represents anything real.

I'll also suggest that everybody casts all over the place in order to
display things properly . An item stored as Boolean doesn't imply we want
F or T as the representation of the value. These are often shown as
True/False, Yes/No or what have you and as such are cast to strings. So I
agree it isn't an issue. Basically see if datetime meets your
requirements much like you check if any other data type meets your
requirements. Am I mistaken in believing that if people need to store
dates for the first 17 centuries AD they aren't doing it using datetime?

Keep mulling :-)
"Tony Van" <to*********@comcast.netwrote in message
news:mI******************************@comcast.com. ..

>All true, but a null date in some areas of my DB are meaningful. I
suppose I can make the MinDate serve as a flag for an empty date. But
you know, I'm already casting all over the place in my code so a cast
from string to datetime seem trivial.

Thanks for the input. as I continue to mull this over.

[Tom Leylan]

Hey Cor that's good information. As you point out however at times we're
still stuck having to represent dates outside of the range provided.
Offsetting the dates certainly works. I think Tony has more to think about
now :-)
"Cor Ligthert [MVP]" <no************@planet.nlwrote in message
news:u6****************@TK2MSFTNGP03.phx.gbl...

Tom,

Be aware that Columbus voyage to America took place before 1582 the year
most modern western countries from that time started with the Georgian
Calendar. The British Empire (including current USA) started with that in
September 1752 therefore dates during that and before that period are very
insecure measured to our current standards.

Although I am probably one of the most datetime puritans in these
newsgroup: I use string dates in our kind of genealogic families website
in a XML file (dataset). The reason is the one you mentioned and because
that I than don't have to change in the browser. I have the information in
a database where I add 2000 to all dates before 1800 and there it are true
datetime variables.

Cor
"Tom Leylan" <ge*@iamtiredofspam.comschreef in bericht
news:%2******************@TK2MSFTNGP05.phx.gbl...

>To be clear I'm not suggesting that you should or shouldn't use the
datetime datatype.

Re: null values, I was also pointing out that one "null" is often not
enough. A null value is generally interpreted as "never been entered"
when in fact that is only one possibility. It may be unknown or
unavailable which may be considered "we entered a null" rather than "it
has a null because we never entered it". There can be "doesn't apply"
type nulls as well as others. These are fairly easy to represent as
coded values but with datetime you have to choose a real datetime to
represent each null subtype.

BTW MinDate is a date that could reasonably represent real data. Given
the range of datetime you can't even store Columbus's voyage to America
as a datetime type. Nothing we tend to store consists of MaxDate values
however and I know of no database that has 12/31/9999 as a date which
represents anything real.

I'll also suggest that everybody casts all over the place in order to
display things properly . An item stored as Boolean doesn't imply we
want F or T as the representation of the value. These are often shown as
True/False, Yes/No or what have you and as such are cast to strings. So
I agree it isn't an issue. Basically see if datetime meets your
requirements much like you check if any other data type meets your
requirements. Am I mistaken in believing that if people need to store
dates for the first 17 centuries AD they aren't doing it using datetime?

Keep mulling :-)
"Tony Van" <to*********@comcast.netwrote in message
news:mI******************************@comcast.com ...

>>All true, but a null date in some areas of my DB are meaningful. I
suppose I can make the MinDate serve as a flag for an empty date. But
you know, I'm already casting all over the place in my code so a cast
from string to datetime seem trivial.

Thanks for the input. as I continue to mull this over.