Date (not datetime) datatype in 2005?

[Larry Bertolini]

For some reason, I recall having read that SQL Server 2005 would support
a datatype that represented date, but not time. (This would be useful
for storing things like birthday, where you usually don't care about the
time of day). But I've got SQL Server 2005 installed, and there's no
such datatype to be found.

Is this something that might be released in a Service Pack, or is it
just not going to happen?

[David Portas]

Larry Bertolini wrote:

For some reason, I recall having read that SQL Server 2005 would support
a datatype that represented date, but not time. (This would be useful
for storing things like birthday, where you usually don't care about the
time of day). But I've got SQL Server 2005 installed, and there's no
such datatype to be found.

Is this something that might be released in a Service Pack, or is it
just not going to happen?

DATE never made it to RTM. It's not in SP1 either.

Instead try:

CREATE TABLE tbl
(dt SMALLDATETIME NOT NULL CHECK
(DATEADD(DAY,DATEDIFF(DAY,0,dt),0)=dt), ...)

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

[Erland Sommarskog]

Larry Bertolini (be*********@osu.edu) writes:

For some reason, I recall having read that SQL Server 2005 would support
a datatype that represented date, but not time. (This would be useful
for storing things like birthday, where you usually don't care about the
time of day). But I've got SQL Server 2005 installed, and there's no
such datatype to be found.

Is this something that might be released in a Service Pack, or is it
just not going to happen?

I definitely hope it's going to happen, but it's not going to happen in
a service pack!

Your recollection is not incorrect. The first beta of SQL 2005 did have a
date data type (and a time data type). However, this was dropped and
that might be just as well. I bet you expect a date type to be compatible
with datetime, work just like datetime with dateadd() and the like.
Instead the date type was implemented in the CLR, and had completely
different syntax and operations, and was not very compatible with
datetime.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Greg D. Moore \(Strider\)]

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...

Larry Bertolini (be*********@osu.edu) writes:

For some reason, I recall having read that SQL Server 2005 would support
a datatype that represented date, but not time. (This would be useful
for storing things like birthday, where you usually don't care about the
time of day). But I've got SQL Server 2005 installed, and there's no
such datatype to be found.

Is this something that might be released in a Service Pack, or is it
just not going to happen?
I definitely hope it's going to happen, but it's not going to happen in
a service pack!

Your recollection is not incorrect. The first beta of SQL 2005 did have a
date data type (and a time data type). However, this was dropped and
that might be just as well. I bet you expect a date type to be compatible
with datetime, work just like datetime with dateadd() and the like.
Instead the date type was implemented in the CLR, and had completely
different syntax and operations, and was not very compatible with
datetime.

Argh, and just today I was wondering about this in 2005.

Oh well.


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx