Date Only Data Type

I would like to have a field in a table that is a Date only data type instea
d
of the datetime. Could someone offer any advice on this in SQL Server 2005.
ThanksDoesn't exists
You could store YYYYMMDD in a char(8) field instead
http://sqlservercode.blogspot.com/|||No such thing.
http://www.aspfaq.com/2206
"Primera" <Primera@.newsgroups.nospam> wrote in message
news:40b7475e341e8c7e4db4ba7da06@.msnews.microsoft.com...
>I would like to have a field in a table that is a Date only data type
>instead of the datetime. Could someone offer any advice on this in SQL
>Server 2005.
> Thanks
>|||>> I would like to have a field [sic] in a table that is a DATE only data type in
stead of the DATETIME. Could someone offer any advice on this in SQL Serve
r 2005. <<
You might want to learn the basics of RDBMS, so that you do not confuse
fields and columns.
Now, to answer your question: do it the right way! Time is not a point
(Chronons), but a duration expressed as half-open intervals. Thus a
day is really "[yyyy-mm-dd 00:00:00, yyyy-mm-dd 23:59:59.999..)" in the
ISO temporal model.
Read Rick Snodgrass at Univeristy of AZ for more details.|||If you want to treat the data as a date, without a time value, just ignore
the time value when your application updates or selects from the database.
The only time it makes a difference is if you are comparing two date values.
If you always insert/update your date values without specifying the time,
then the time is set to midnight of that day. As long as you are
consistent, this has the same effect as if you didn't store the time at all,
and you wont run into issues when comparing dates.
You could build in a trigger that insures the time value is set to 00:00:00
on inserts and updates as well, which would guarantee consistency even if
the application programming mistakenly sets the time to another value.
The only other real difference is storage, and I doubt it is significant
enough to even give consideration to with today's storage costs.
"Primera" <Primera@.newsgroups.nospam> wrote in message
news:40b7475e341e8c7e4db4ba7da06@.msnews.microsoft.com...
> I would like to have a field in a table that is a Date only data type
instead
> of the datetime. Could someone offer any advice on this in SQL Server
2005.
> Thanks
>|||> The only other real difference is storage, and I doubt it is significant
> enough to even give consideration to with today's storage costs.
Well, I would suggest that if you only care about the date, then using
SMALLDATETIME will minimize storage requirements and make indexes more
efficient, and I don't know of any downside...
A|||I would agree with others that a Date only type has only marginal value as
you could treat all datetimes as 00:00:00.000 time to get the same result.
However, a Date only type can be mentally easier to deal with as you know
time can not effect what your doing. I actually added a TDate and TTime
UDTs to a sql project I did. Can use it as is or update it as needed if you
don't like the api choices. Get the project at link below.
http://channel9.msdn.com/ShowPost.aspx?PostID=147390
William Stacey [MVP]
"Primera" <Primera@.newsgroups.nospam> wrote in message
news:40b7475e341e8c7e4db4ba7da06@.msnews.microsoft.com...
>I would like to have a field in a table that is a Date only data type
>instead of the datetime. Could someone offer any advice on this in SQL
>Server 2005.
> Thanks
>|||William Stacey [MVP] (william.stacey@.gmail.com) writes:
> I would agree with others that a Date only type has only marginal value as
I don't think so. People have been screaming for this for many years,
and I would very disappointed if it is not in the next version of SQL
Server.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||With 2005, they don't have to wait, unless they want to. I agree it has
some value, but maybe I missed a whole bunch of use cases. What are some of
the more profound use cases for Date only you have come across? TIA Erland.
William Stacey [MVP]
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns974953149317Yazorman@.127.0.0.1...
> William Stacey [MVP] (william.stacey@.gmail.com) writes:
> I don't think so. People have been screaming for this for many years,
> and I would very disappointed if it is not in the next version of SQL
> Server.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||> With 2005, they don't have to wait, unless they want to.
Why? The Date and Time datatypes were dropped from the product very early
on. I think most of it was due to the volume with which we complained about
its implementation (and by we I do mean myself, Erland and others). I'm
sure that's not what you mean, so while, yes, you could create your own UDT,
have fun with that, and let us know when you have something marginally
useful! The most complex UDT I've seen to date that was actually useable
was POINT. There is so much involved with date validation and
interoperability that it is unlikely you would be able to develop something
that would seamlessly integrate with the rest of the product (most notably
implicit conversion to/from datetime and acceptance as inputs to functions
like DATEDIFF/DATEADD/YEAR/MONTH/DAY).

> What are some of the more profound use cases for Date only you have come
> across?
A calendar table. Hire/fire date. Birth date. I'm sure if I spent more
than two minutes and reviewed all of the projects I've been involved with in
the past 10 years, I could come up with dozens of others. None of these
need time in most cases, and life would be much simpler if we didn't have to
truncate/validate/correct data going in or coming out, or when comparing, or
when displaying, or when exporting to XML, or ...