September 1, 2015 at 9:51 am
drop table #temp
CREATE TABLE #TEMP
(
date1 DATE,
date2 DATE
)
insert into #TEMP values
('2011-06-18','2011-06-29'),('2010-12-09','2010-12-15'),('2011-03-04','2011-03-16'),('2010-02-09','2010-07-08'),('2010-03-10','2010-03-31'),
('2010-03-31',null),('2014-08-01',null),(null,'2010-07-08'),('2010-07-08',null),(null,'2014-05-23'),
('2011-02-09','2011-02-02'),('2011-06-22','2011-06-14'),(null,null),(null,null)
SELECT date1, date2,
CASE
WHEN (ISNULL(date1, '1900-01-01') = '1900-01-01' AND ISNULL(date2, '1900-01-01') = '1900-01-01') THEN 0
WHEN ISNULL(date2, '1900-01-01') = '1900-01-01' THEN DATEDIFF(DAY, date1, GETDATE())
WHEN ISNULL(date1, '1900-01-01') = '1900-01-01' THEN 0
ELSE
DATEDIFF(DAY, date1, date2)
END AS PaymentDays
FROM #temp WITH (NOLOCK)
In above, in "WHEN (ISNULL(date1, '1900-01-01') = '1900-01-01' AND ISNULL(date2, '1900-01-01') = '1900-01-01') THEN 0" condition instead of 0 I want blank ('') but it saying since we are substracting, it won't accept blank in int column.
In "WHEN ISNULL(date1, '1900-01-01') = '1900-01-01' THEN 0" case also want blank.
Any suggestion.
September 1, 2015 at 10:06 am
Munabhai (9/1/2015)
drop table #tempCREATE TABLE #TEMP
(
date1 DATE,
date2 DATE
)
insert into #TEMP values
('2011-06-18','2011-06-29'),('2010-12-09','2010-12-15'),('2011-03-04','2011-03-16'),('2010-02-09','2010-07-08'),('2010-03-10','2010-03-31'),
('2010-03-31',null),('2014-08-01',null),(null,'2010-07-08'),('2010-07-08',null),(null,'2014-05-23'),
('2011-02-09','2011-02-02'),('2011-06-22','2011-06-14'),(null,null),(null,null)
SELECT date1, date2,
CASE
WHEN (ISNULL(date1, '1900-01-01') = '1900-01-01' AND ISNULL(date2, '1900-01-01') = '1900-01-01') THEN 0
WHEN ISNULL(date2, '1900-01-01') = '1900-01-01' THEN DATEDIFF(DAY, date1, GETDATE())
WHEN ISNULL(date1, '1900-01-01') = '1900-01-01' THEN 0
ELSE
DATEDIFF(DAY, date1, date2)
END AS PaymentDays
FROM #temp WITH (NOLOCK)
In above, in "WHEN (ISNULL(date1, '1900-01-01') = '1900-01-01' AND ISNULL(date2, '1900-01-01') = '1900-01-01') THEN 0" condition instead of 0 I want blank ('') but it saying since we are substracting, it won't accept blank in int column.
In "WHEN ISNULL(date1, '1900-01-01') = '1900-01-01' THEN 0" case also want blank.
Any suggestion.
Quick point, all output of the case statement have to be of a compatible data type.
😎
September 1, 2015 at 10:10 am
If I use, as following, it won't throw error, but it will display 0 instead of blank.
WHEN ISNULL(date1, '1900-01-01') = '1900-01-01' THEN ''
September 1, 2015 at 10:20 am
A CASE statement can only return one data type. If possible, it will implicitly convert the values following the data type precedence rules. In this case, the empty string is converted to an int following the rules. If you want to prevent this, convert the datediff result into a string or format the 0 as blank in your front-end.
--This
SELECT date1,
date2,
CASE
WHEN ISNULL(date1, '1900-01-01') = '1900-01-01' THEN ''
ELSE CAST( DATEDIFF(DAY, date1, ISNULL( NULLIF( date2, '1900-01-01'), GETDATE())) AS varchar(6))
END AS PaymentDays
FROM #temp;
September 1, 2015 at 10:20 am
Munabhai (9/1/2015)
If I use, as following, it won't throw error, but it will display 0 instead of blank.WHEN ISNULL(date1, '1900-01-01') = '1900-01-01' THEN ''
Please learn to be consistent in your coding. In your OP you use #TEMP and #temp interchangeably. This works in a case insensitive environment but fails in a case sensitive environment. Being consistent demonstrates you care about the code you write and that your code will run in either environment.
September 1, 2015 at 11:33 am
Munabhai (9/1/2015)
drop table #tempCREATE TABLE #TEMP
(
date1 DATE,
date2 DATE
)
insert into #TEMP values
('2011-06-18','2011-06-29'),('2010-12-09','2010-12-15'),('2011-03-04','2011-03-16'),('2010-02-09','2010-07-08'),('2010-03-10','2010-03-31'),
('2010-03-31',null),('2014-08-01',null),(null,'2010-07-08'),('2010-07-08',null),(null,'2014-05-23'),
('2011-02-09','2011-02-02'),('2011-06-22','2011-06-14'),(null,null),(null,null)
SELECT date1, date2,
CASE
WHEN (ISNULL(date1, '1900-01-01') = '1900-01-01' AND ISNULL(date2, '1900-01-01') = '1900-01-01') THEN 0
WHEN ISNULL(date2, '1900-01-01') = '1900-01-01' THEN DATEDIFF(DAY, date1, GETDATE())
WHEN ISNULL(date1, '1900-01-01') = '1900-01-01' THEN 0
ELSE
DATEDIFF(DAY, date1, date2)
END AS PaymentDays
FROM #temp WITH (NOLOCK)
In above, in "WHEN (ISNULL(date1, '1900-01-01') = '1900-01-01' AND ISNULL(date2, '1900-01-01') = '1900-01-01') THEN 0" condition instead of 0 I want blank ('') but it saying since we are substracting, it won't accept blank in int column.
In "WHEN ISNULL(date1, '1900-01-01') = '1900-01-01' THEN 0" case also want blank.
Any suggestion.
Why in the world are you using NOLOCK here? There is no possibility that table can be locked by any other process. Are you familiar with that hint and everything it brings to the table? http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 1, 2015 at 11:41 am
Sean Lange (9/1/2015)
Munabhai (9/1/2015)
drop table #tempCREATE TABLE #TEMP
(
date1 DATE,
date2 DATE
)
insert into #TEMP values
('2011-06-18','2011-06-29'),('2010-12-09','2010-12-15'),('2011-03-04','2011-03-16'),('2010-02-09','2010-07-08'),('2010-03-10','2010-03-31'),
('2010-03-31',null),('2014-08-01',null),(null,'2010-07-08'),('2010-07-08',null),(null,'2014-05-23'),
('2011-02-09','2011-02-02'),('2011-06-22','2011-06-14'),(null,null),(null,null)
SELECT date1, date2,
CASE
WHEN (ISNULL(date1, '1900-01-01') = '1900-01-01' AND ISNULL(date2, '1900-01-01') = '1900-01-01') THEN 0
WHEN ISNULL(date2, '1900-01-01') = '1900-01-01' THEN DATEDIFF(DAY, date1, GETDATE())
WHEN ISNULL(date1, '1900-01-01') = '1900-01-01' THEN 0
ELSE
DATEDIFF(DAY, date1, date2)
END AS PaymentDays
FROM #temp WITH (NOLOCK)
In above, in "WHEN (ISNULL(date1, '1900-01-01') = '1900-01-01' AND ISNULL(date2, '1900-01-01') = '1900-01-01') THEN 0" condition instead of 0 I want blank ('') but it saying since we are substracting, it won't accept blank in int column.
In "WHEN ISNULL(date1, '1900-01-01') = '1900-01-01' THEN 0" case also want blank.
Any suggestion.
Why in the world are you using NOLOCK here? There is no possibility that table can be locked by any other process. Are you familiar with that hint and everything it brings to the table? http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/[/url]
Since it's impossible that anyone else needs to use that table, why on earth would you not use NOLOCK to reduce overhead?
Edit: Corrected one typo.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 1, 2015 at 11:47 am
ScottPletcher (9/1/2015)
Sean Lange (9/1/2015)
Munabhai (9/1/2015)
drop table #tempCREATE TABLE #TEMP
(
date1 DATE,
date2 DATE
)
insert into #TEMP values
('2011-06-18','2011-06-29'),('2010-12-09','2010-12-15'),('2011-03-04','2011-03-16'),('2010-02-09','2010-07-08'),('2010-03-10','2010-03-31'),
('2010-03-31',null),('2014-08-01',null),(null,'2010-07-08'),('2010-07-08',null),(null,'2014-05-23'),
('2011-02-09','2011-02-02'),('2011-06-22','2011-06-14'),(null,null),(null,null)
SELECT date1, date2,
CASE
WHEN (ISNULL(date1, '1900-01-01') = '1900-01-01' AND ISNULL(date2, '1900-01-01') = '1900-01-01') THEN 0
WHEN ISNULL(date2, '1900-01-01') = '1900-01-01' THEN DATEDIFF(DAY, date1, GETDATE())
WHEN ISNULL(date1, '1900-01-01') = '1900-01-01' THEN 0
ELSE
DATEDIFF(DAY, date1, date2)
END AS PaymentDays
FROM #temp WITH (NOLOCK)
In above, in "WHEN (ISNULL(date1, '1900-01-01') = '1900-01-01' AND ISNULL(date2, '1900-01-01') = '1900-01-01') THEN 0" condition instead of 0 I want blank ('') but it saying since we are substracting, it won't accept blank in int column.
In "WHEN ISNULL(date1, '1900-01-01') = '1900-01-01' THEN 0" case also want blank.
Any suggestion.
Why in the world are you using NOLOCK here? There is no possibility that table can be locked by any other process. Are you familiar with that hint and everything it brings to the table? http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/[/url]
Since it's impossible that anyone else needs to use that table, why on earth would you not use NOLOCK to reduce overhead?
Edit: Corrected one typo.
Scott last I remembered in the thread where you and Jeff were discussing this you were not able to provide significant proof that it made much, if any, difference.
You have been around this technology long enough to know that when you see that hint it is very frequently an indication that the person doesn't understand the nuances of that hint. As you well know that hint is not a magic go faster button but you seem to not only condone its usage, you seem to promote using it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 1, 2015 at 11:58 am
Sean Lange (9/1/2015)
ScottPletcher (9/1/2015)
Sean Lange (9/1/2015)
Munabhai (9/1/2015)
drop table #tempCREATE TABLE #TEMP
(
date1 DATE,
date2 DATE
)
insert into #TEMP values
('2011-06-18','2011-06-29'),('2010-12-09','2010-12-15'),('2011-03-04','2011-03-16'),('2010-02-09','2010-07-08'),('2010-03-10','2010-03-31'),
('2010-03-31',null),('2014-08-01',null),(null,'2010-07-08'),('2010-07-08',null),(null,'2014-05-23'),
('2011-02-09','2011-02-02'),('2011-06-22','2011-06-14'),(null,null),(null,null)
SELECT date1, date2,
CASE
WHEN (ISNULL(date1, '1900-01-01') = '1900-01-01' AND ISNULL(date2, '1900-01-01') = '1900-01-01') THEN 0
WHEN ISNULL(date2, '1900-01-01') = '1900-01-01' THEN DATEDIFF(DAY, date1, GETDATE())
WHEN ISNULL(date1, '1900-01-01') = '1900-01-01' THEN 0
ELSE
DATEDIFF(DAY, date1, date2)
END AS PaymentDays
FROM #temp WITH (NOLOCK)
In above, in "WHEN (ISNULL(date1, '1900-01-01') = '1900-01-01' AND ISNULL(date2, '1900-01-01') = '1900-01-01') THEN 0" condition instead of 0 I want blank ('') but it saying since we are substracting, it won't accept blank in int column.
In "WHEN ISNULL(date1, '1900-01-01') = '1900-01-01' THEN 0" case also want blank.
Any suggestion.
Why in the world are you using NOLOCK here? There is no possibility that table can be locked by any other process. Are you familiar with that hint and everything it brings to the table? http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/[/url]
Since it's impossible that anyone else needs to use that table, why on earth would you not use NOLOCK to reduce overhead?
Edit: Corrected one typo.
Scott last I remembered in the thread where you and Jeff were discussing this you were not able to provide significant proof that it made much, if any, difference.
You have been around this technology long enough to know that when you see that hint it is very frequently an indication that the person doesn't understand the nuances of that hint. As you well know that hint is not a magic go faster button but you seem to not only condone its usage, you seem to promote using it.
Only because you seem to blindly disparage it no matter what the context. NOLOCK wouldn't exist in SQL if it didn't have an effect. Any lock requires resources to accomplish: that's axiomatic. Thus avoiding any lock saves resources: that's axiomatic as well. Are those resources significant? Impossible to say without a lot of details.
In this specific case, how could NOLOCK ever cause a consistency problem? I guess I just don't see how.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 1, 2015 at 12:02 pm
Thanks all. Special thanks to Luis C.
I got solution with the help of Luis C.
SELECT date1, date2,
CASE
WHEN (ISNULL(date1, '1900-01-01') = '1900-01-01' AND ISNULL(date2, '1900-01-01') = '1900-01-01') THEN CAST('' AS VARCHAR(6))
WHEN ISNULL(date1, '1900-01-01') = '1900-01-01' THEN ''
WHEN ISNULL(date2, '1900-01-01') = '1900-01-01' THEN CAST( DATEDIFF(DAY, date1,GETDATE()) AS varchar(6))
ELSE
CAST(DATEDIFF(DAY, date1, date2) AS VARCHAR(6))
END AS PaymentDays
FROM #temp;
September 1, 2015 at 12:11 pm
I know it's rather off-topic, but it's an interesting diversion, so I thought I'd just chime in on NOLOCK and temporary tables.
SELECT queries against temporary tables typically only take a shared object lock anyway, so it's really just the difference between taking an S lock on the object and a Sch-S lock on the object. There's really no additional overhead in terms of the number of locks taken, as that is quite low in both cases.
Aaron Bertrand took a quick look at that here: http://blogs.sqlsentry.com/aaronbertrand/nolock-temp-tables/.
The first time you run them you'll see a bunch of locks on metadata and such for the query to compile (you see that in Aaron's piece above), but afterwards on a local temporary table that is a heap you'll see an S lock an an IS lock for the object without the NOLOCK hint (or directive, if you prefer), and with NOLOCK you'll see a Sch-S lock on the object and an S lock on the HOBT. With a clustered index, the results are the same except that you won't see the S lock on the HOBT for the NOLOCK case. I'm not sure that the difference between two locks and one lock when you have a clustered index on the temp table justifies the use of NOLOCK here 🙂
Basically, it just doesn't really make a difference with local temporary tables, so while you're technically not doing much other than wasting a few keystrokes, I can see how it might have some negative effects in terms of perpetuating some bad practices and misconceptions.
Cheers!
September 1, 2015 at 12:27 pm
Actually, I was working with some big table, while coping code, I forgot to remove (nolock). Sorry for that confusion. But, I got solution what I am looking for. Thanks you all.
September 1, 2015 at 12:39 pm
Even with large tables, NOLOCK hints aren't the best option. I'd suggest you to read more about this hint and understand how this is a poor idea.
Back to the original problem, my CASE statement will give you the same results than yours with fewer conditions. If your date2 column won't ever be '1900-01-01', you can replace NULLIF( date2, '1900-01-01') with date2.
September 1, 2015 at 12:40 pm
Thanks
September 1, 2015 at 12:42 pm
Jacob Wilkins (9/1/2015)
I know it's rather off-topic, but it's an interesting diversion, so I thought I'd just chime in on NOLOCK and temporary tables.SELECT queries against temporary tables typically only take a shared object lock anyway, so it's really just the difference between taking an S lock on the object and a Sch-S lock on the object. There's really no additional overhead in terms of the number of locks taken, as that is quite low in both cases.
Aaron Bertrand took a quick look at that here: http://blogs.sqlsentry.com/aaronbertrand/nolock-temp-tables/.
The first time you run them you'll see a bunch of locks on metadata and such for the query to compile (you see that in Aaron's piece above), but afterwards on a local temporary table that is a heap you'll see an S lock an an IS lock for the object without the NOLOCK hint (or directive, if you prefer), and with NOLOCK you'll see a Sch-S lock on the object and an S lock on the HOBT. With a clustered index, the results are the same except that you won't see the S lock on the HOBT for the NOLOCK case. I'm not sure that the difference between two locks and one lock when you have a clustered index on the temp table justifies the use of NOLOCK here 🙂
Basically, it just doesn't really make a difference with local temporary tables, so while you're technically not doing much other than wasting a few keystrokes, I can see how it might have some negative effects in terms of perpetuating some bad practices and misconceptions.
Cheers!
SQL does take many fewer locks in tempdb for SQL 2012 forward. AFAIK, SQL did not do that in SQL 2008 and before. (Not sure about 2008 R2). I thought this forum was for SQL 2008 (or perhaps before). In older versions, SQL acquires shared locks row by row for SELECTs, in tempdb as with any other db. Naturally it must also release those locks: admittedly, that is so little overhead it can probably be ignored. I.e., a 100K rows SELECTed = a 100K locks. AFAIK, if you run the code in the article yourself on a SQL 2008 box, you should be able to see the row locks.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply