Date only fields in SQL Server

[Mystery Man]

Does anyone know if Microsoft is planning to add a DATE only data type
to SQLServer.

I know that you could use a datetime and convert/cast or use datepart
to compare, but this can be tedious and error prone.

What is the recommended way to compare date-only fields?

eg if convert(char(11), @date_field) = convert(getdate(), @date_field)
-- do something??

[Simon Hayes]

"Mystery Man" <Pr************@hotmail.com> wrote in message
news:87**************************@posting.google.c om...

Does anyone know if Microsoft is planning to add a DATE only data type
to SQLServer.

I know that you could use a datetime and convert/cast or use datepart
to compare, but this can be tedious and error prone.

What is the recommended way to compare date-only fields?

eg if convert(char(11), @date_field) = convert(getdate(), @date_field)
-- do something??

There's an article in the November 2003 edition of SQL Server Magazine about
TSQL enhancements in Yukon, according to which the answer is yes.

http://www.sqlmag.com/Articles/Index...rticleID=40206

As for comparing dates only, you have to use one of the options you noted
above - DATEPART() or CONVERT():

if convert(char(8), col1, 112) = convert(char(8), col2, 112)
begin
...
end

If your application only uses dates, not times, you may be able to assume
that all times are 00:00.000, in which case you can always compare datetime
values directly. But this is a potentially risky assumption, unless you're
sure that all data entry enforces this rule.

Simon

[louis nguyen]

> What is the recommended way to compare date-only fields?

eg if convert(char(11), @date_field) = convert(getdate(), @date_field)
-- do something??

I tend to use datediff:

if datediff('day',@date1,@date2) = 0 begin ... end

[Erland Sommarskog]

Mystery Man (Pr************@hotmail.com) writes:

What is the recommended way to compare date-only fields?

datecol = @date

Most of our date columns are of the type aba_date, which is datetime,
with this rule bound to it:

CREATE RULE aba_date AS convert(char(8), @x, 112) = @x

And we trust our parameters to be date values.

I should add that we rarely have reason to look at getdate() to get
the current day; we get that from a parameter table, because our
system changes day when it runs its night job, which may not be at
midnight. getdate() is only used for auditing.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp