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??"Mystery Man" <PromisedOyster@.hotmail.com> wrote in message
news:87c81238.0402100504.7966b095@.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/Inde...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|||> 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|||Mystery Man (PromisedOyster@.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, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
