If you're not averse to having a function do the dirty work, this helps make the statement cleaner:
CREATE FUNCTION LocalDateFromUTCTime
(
@UTCDateTime datetime
)
RETURNS datetime
BEGIN
DECLARE @diff int;
SET @diff = datediff(hh,GetUTCDate(), GetDate());
RETURN DATEADD(day, DATEDIFF(day, 0, DATEADD(hh, @diff, @UTCDateTime)),0);
END
You could then do something like:
SELECT dbo.LocalDateFromUTCTime(MyUTCDate), COUNT(*)
FROM MyTable
GROUP BY dbo.LocalDateFromUTCTime(MyUTCDate);
This does, of course, make the statement non-SARGable.
If sargability is of primary concern due to the large number of records you may have, you could create a materialized view with an index on the calculated field.
EDIT:
Per @RichardThekiwi the SARGability of this particular statement is not affected by use of the function since the function is not part of either a JOIN
or a WHERE
clause. Where the function is used, it is ran after any indexes would have been used.
ALSO, not the above code will truncate any time portion of the date input to the function (this is by design). Therefore, any time zones that implement minutes such as the Prince Edward Island in Canada (UTC -4:30), India (UTC -4:30), and Kathmandu (UTC +5:45)
My initial answer suggested that the ANSI_PADDING flag set to OFF may be to blame for the difference in behavior. However, this is incorrect; this flag only has an effect on storage, but not equality comparison.
The difference stems from Microsoft's implementation of the SQL standard. The standard states that when checking for equality, both strings left and right of the equality operator have to be padded to have the same length. This explains the following results:
insert into test_padding (varchar_clmn, nvarchar_clmn) values ('space ', 'nspace ')
go
-- equality for varchar column
select count(*) from test_padding where varchar_clmn = 'space' -- returns 1
select count(*) from test_padding where varchar_clmn = 'space ' -- returns 1
select count(*) from test_padding where varchar_clmn = 'space ' --returns 1
-- equality for nvarchar column
select count(*) from test_padding where nvarchar_clmn = 'nspace' -- returns 1
select count(*) from test_padding where nvarchar_clmn = 'nspace ' -- returns 1
select count(*) from test_padding where nvarchar_clmn = 'nspace ' --returns 1
The LIKE operator does not pad its operands. It also behaves differently for VARCHAR
and NVARCHAR
column types:
-- likeness for varchar column
select count(*) from test_padding where varchar_clmn like 'space' -- returns 1
select count(*) from test_padding where varchar_clmn like 'space ' -- returns 1
select count(*) from test_padding where varchar_clmn like 'space ' -- returns 0
-- likeness for nvarchar column
select count(*) from test_padding where nvarchar_clmn like 'nspace' -- returns 0
select count(*) from test_padding where nvarchar_clmn like 'nspace ' -- returns 1
select count(*) from test_padding where nvarchar_clmn like 'nspace ' -- returns 0
The behavior of the LIKE operator for the ASCII type is SQL Server-specific; for the Unicode type it is ANSI-compliant.
Best Answer
The (formerly) accepted answer
iswas incorrect as itiswas a bad and misleading test. The two queries being compared do not do the same thing due to a simple typo that causes them to not be an apples-to-apples comparison. The test in the accepted answer is unfairly biased in favor of theCAST
operation. The issue is that theCONVERT
operation is being done withconvert(date, GETDATE()+num,20)
-- a value to convert that changes per row -- while theCAST
operation is being done with a simplecast(GETDATE() as date)
-- a value to convert that is consistent across all rows and is replaced in the execution plan as a constant. And in fact, looking at the XML execution plan even shows the actual operation performed as beingCONVERT(date,getdate(),0)
!!Insofar as my testing shows (after making them equal via using
cast(GETDATE()+num as date)
), the times varry with them being mostly the same (which makes sense if they are both reduced to beingCONVERT
anyway) or theCONVERT
winning:The main difference between CAST and CONVERT is that
CONVERT
allows for the "style" to be specified. The "style" not only allows for tailoring the output when converting a non-string to a string, but also allows for specifying the input format when converting a string to a non-string:Now compare that functionally with
CAST
:One additional thing to mention about
CAST
: because it does not have the "style" parameter, the format of the date string passed in is assumed to be that of the current culture (a session property). The current culture is denoted by the@@LANGID
and@@LANGUAGE
system variables. This means that theCAST
statement that failed in the test directly above could succeed for a different culture / language. The following tests shows this behavior and how that same date string does work withCAST
when the current language is "French" (and would work with several others, based on the values in thedateformat
column insys.syslanguages
):