Words: 717
Time to read: ~3.5 minutes
Input:
I’m just back from holidays for the last week and a bit, which was helpful as September was a really busy month for me (partly because of this) so I’ve fallen behind on blog posts. I know, I know, I should have had a few ready to fire in the chamber and scheduled but I didn’t.
In fact, I wasn’t even going to post something this week, and was just going to spend the time catching up on reading blogs, catching up on study, and responding to emails (oh god, the amount of emails!), but then my Senior asked me a question about a query and surprised me with the answer. With material like that, how could I not document it!
Process:
Let’s work on a MCVE for everyone.
We have a table called dbo.RunningTotals
with two columns, a date
column called SpentDate
and a smallmoney
column called SpentAmount
.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE TABLE dbo.RunningTotals ( | |
SpentDate date NOT NULL, | |
SpentAmount smallmoney NOT NULL | |
); | |
GO | |
Now let’s throw some sample data in there…
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
INSERT INTO dbo.RunningTotals (SpentDate, SpentAmount) VALUES (DATEADD(DAY, –1, GETDATE()), 50.00); | |
INSERT INTO dbo.RunningTotals (SpentDate, SpentAmount) VALUES (DATEADD(DAY, –1, GETDATE()), 50.00); | |
INSERT INTO dbo.RunningTotals (SpentDate, SpentAmount) VALUES (DATEADD(DAY, –1, GETDATE()), –75.00); | |
INSERT INTO dbo.RunningTotals (SpentDate, SpentAmount) VALUES (DATEADD(DAY, –1, GETDATE()), 50.00); | |
INSERT INTO dbo.RunningTotals (SpentDate, SpentAmount) VALUES (GETDATE(), 100.00); | |
INSERT INTO dbo.RunningTotals (SpentDate, SpentAmount) VALUES (GETDATE(), 100.00); | |
INSERT INTO dbo.RunningTotals (SpentDate, SpentAmount) VALUES (GETDATE(), –150.00); | |
INSERT INTO dbo.RunningTotals (SpentDate, SpentAmount) VALUES (GETDATE(), 100.00); | |
SELECT * FROM dbo.RunningTotals; |
Now, don’t be fooled…
…I know that the main difference that people think of when comparing ISNULL
and COALESCE
is that the former only allows two values while the later allows multiple i.e. ISNULL(x1, x2)
vs COALESCE(x1, x2, ..., xn)
That’s not what I’m trying to point out today. Let’s give you the sample code first.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
DECLARE @Date date = DATEADD(DAY, –1, GETDATE()); | |
SELECT SpentDate, | |
SUM(SpentAmount) AS Total | |
FROM dbo.RunningTotals | |
WHERE SpentDate >= ISNULL(@Date, GETDATE()) | |
GROUP BY | |
GROUPING SETS( | |
SpentDate, — Per date, | |
() — To include the overall total as well. | |
); | |
GO | |
DECLARE @Date date = NULL; | |
SELECT SpentDate, | |
SUM(SpentAmount) AS Total | |
FROM dbo.RunningTotals | |
WHERE SpentDate >= ISNULL(@Date, GETDATE()) | |
GROUP BY | |
GROUPING SETS( | |
SpentDate, | |
() | |
); | |
GO | |
DECLARE @Date date = DATEADD(DAY, –1, GETDATE()); | |
SELECT SpentDate, | |
SUM(SpentAmount) AS Total | |
FROM dbo.RunningTotals | |
WHERE SpentDate >= COALESCE(@Date, GETDATE()) | |
GROUP BY | |
GROUPING SETS( | |
SpentDate, | |
() | |
); | |
GO | |
DECLARE @Date date = NULL; | |
SELECT SpentDate, | |
SUM(SpentAmount) AS Total | |
FROM dbo.RunningTotals | |
WHERE SpentDate >= COALESCE(@Date, GETDATE()) | |
GROUP BY | |
GROUPING SETS( | |
SpentDate, | |
() | |
); | |
GO |
What we have here are 4 tests; the first 2 are with ISNULL
with and without a variable supplied while the last 2 use COALESCE
, again with and without a variable supplied.
Test 1: ISNULL with variable
First, we are going to pass in yesterday’s date…

…and we have a total for both days along with a overall total. Next let’s see what happens if we pass in a NULL.
Test 2: ISNULL without variable
When we pass a NULL into the variable, the ISNULL
is going to ignore our variable and use the GETDATE()
function instead.

So, overall great results. When we have a non null value, we’re good; when we have a null value, we’re still good!
Now for COALESCE
…
Test 3: COALESCE with variable
All we are changing now is using COALESCE
instead of ISNULL
and we’re going to pass in yesterdays date.

No change, nothing to worry about! Now let’s pass in a null variable.
Test 4: COALESCE without variable
When we pass a NULL into the variable, the COALESCE
is going to bypass our variable and use the GETDATE()
function instead.

You may be asking yourself “What the…?!”.
I asked myself the same question but then I thought of a better idea and asked my Senior the question instead (he usually has better answers than I do), who proceeded to tell me that the final test would only work exactly at midnight!
….so I repeated my question to him.
Output:
Microsoft docs helped shed some light on this:
Data type determination of the resulting expression is different.
ISNULL
uses the data type of the first parameter,COALESCE
follows theCASE
expression rules and returns the data type of value with the highest precedence.
So with our ISNULL
example, when we passed in a null, the ISNULL saw that our variable was a date
data type and implicitly converted the GETDATE()
function into a date.
This was not the case with our COALESCE
test!
When we passed in our null value, the COALESCE saw that GETDATE()
returns a datetime
, it measured that data type against the date
data type of our variable and checked which one came first in our data type precedence (I must bookmark that page).
This means that our WHERE
clause is looking for any values which match the exact date and time! Since we have no values greater than the start of today in our table, we return no results.
A picture says a thousand words though (and I’m nearly at that now)…

All Together Now!
Before now, I would have said that the main difference between ISNULL
and COALESCE
would just have been the number of arguments that they accept. I’m going to have to watch out for the data types as well now!
It’s still nice to know how surprising SQL Server can be. 🙂
Hi thanks for postting this
On a tangent, a few months ago I indirectly discovered that the ISNULL function evaluates the second expression unconditionally even if the first expression turns out to be non-null, while the COALESCE evaluates the second expression if, and only if, the first turns out to be null. If the expressions are themselves subqueries, this has potentially significant performance implications.
Nice! It may be the way that the COALESCE is a CASE statement underneath that could explain it but that’s good to know!
I was a little surprised to find out the datatype rules of both ISNULL and COALESCE. The latter is closer to meeting my expectation, but still different. Thanks for enlightening me!
No worries, hope it helped!
so can (or should) the final test be fixed by wrapping within as CAST ( GETDATE() as date) ?
Quick answer…Sure, that works!
Do that and you’ll have a nice little work-around.
This is interesting. I find the subtle bug fascinating, but can I ask which version? I find this behavior with ISNULL, not COALESCE. Here’s my repro on SQL 2016, SP1
CREATE TABLE MySales
( SaleDate DATETIME
, SaleAmount MONEY
)
INSERT MySales (SaleDate, SaleAmount)
VALUES
(‘20171002 9:00am’, 2000),
(‘20171002 10:00am’, 1000),
(‘20171003 2:00pm’, 1200),
(‘20171004 4:59pm’, 1800)
GO
— Get an outlier today
INSERT dbo.MySales (SaleDate, SaleAmount) VALUES (GETDATE(), 25000)
GO
CREATE OR ALTER PROCEDURE GetMyReport @dt DATE = NULL
AS
SELECT
[Coalesce] = ‘Coalesce’
, ms.SaleDate
, ms.SaleAmount
FROM dbo.MySales AS ms
WHERE saledate <= COALESCE(@dt, GETDATE())
;
GO
GO
EXEC GetMyReport
GO
CREATE OR ALTER PROCEDURE GetMyReport @dt DATE = NULL
AS
SELECT
IsNull = ‘Isnull’
, ms.SaleDate
, ms.SaleAmount
FROM dbo.MySales AS ms
WHERE saledate <= ISNULL(@dt, GETDATE())
;
GO
EXEC GetMyReport
;
DECLARE @dt DATE = NULL
;
SELECT
ms.SaleDate
, ms.SaleAmount
, ‘Coalesce’ = COALESCE(@dt, GETDATE())
, ‘isnull’ = ISNULL(@dt, GETDATE())
FROM dbo.MySales AS ms
WHERE saledate <= ISNULL(@dt, GETDATE())
;
Hi Steve,
Originally this was run on SQL Server 2014, but just ran it on SQL Server 2016 SP1-CU13 and works there.
As far as I can tell, if you switch the operator to be greater than it should work…provided you throw some data after
GETDATE()
in there that will get spit out.Let me know!
Unless it’s also because you made it a stored procedure parameter…and the column a datetime instead of a date…
Ohhh so many questions!