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]

Re: Date only fields in SQL Server


"Mystery Man" <PromisedOyster @hotmail.com> wrote in message
news:87c81238.0 402100504.7966b 095@posting.goo gle.com...[color=blue]
> 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??[/color]

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.cfm?ArticleID=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]

Re: Date only fields in SQL Server

> What is the recommended way to compare date-only fields?[color=blue]
>
> eg if convert(char(11 ), @date_field) = convert(getdate (), @date_field)
> -- do something??[/color]
I tend to use datediff:

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

[Erland Sommarskog]

Re: Date only fields in SQL Server

Mystery Man (PromisedOyster @hotmail.com) writes:[color=blue]
> What is the recommended way to compare date-only fields?[/color]

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, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp