# ADD and SUBTRACT depending on the condition is CASE STATEMENT

• ### Question

• ```SELECT
CASE WHEN  dbo.VU_HRG2.CC_Start_Date_2 = dbo.VU_HRG2.CC_Dis_Date_1 AND dbo.VU_HRG2.CCBDay1 is not NULL THEN
'TRUE'--ISNULL(dbo.VU_HRG2.CCBedDay2,0) - 1
ELSE
'FALSE'--ISNULL(dbo.VU_HRG2.CCBedDay1,0) + 1
END AS CCBDay1
FROM         dbo.VU_HRG2```
In this statement the values in the columns are as follows:
CC_Start_Date_2 = 28-03-2009
CC_Dis_Date_1 = 28-03-2009
CCBDay1 = 1
CCBDay2 = 1
According to these values the first part of a statement should return True. It is returning False which mean it is going in ELSE Part of the case statement. why it is doing that? any idea?

For the time being i have commented the add and subtract statement just to verify the condition is working. are these ADD and SUBTRACT Functions fine?
If uncomment the statement instead of TRUE then it should return  1-1 = 0
Friday, January 15, 2010 9:41 AM

• Something like this?

```SELECT
CASE
WHEN  DATEDIFF(D,dbo.VU_HRG2.CC_Start_Date_2, dbo.VU_HRG2.CC_Dis_Date_1) = 0 AND dbo.VU_HRG2.CCBDay1 IS NOT NULL THEN
'TRUE'
ELSE
END AS CCBDay1
FROM         dbo.VU_HRG2
```

every day is a school day
• Proposed as answer by Friday, January 15, 2010 10:13 AM
• Marked as answer by Friday, January 15, 2010 1:20 PM
Friday, January 15, 2010 9:58 AM
• >>If i will use datediff function then it will return me the numbers not date. numbers can be different. Why it is not comparing the date values?

you want to compare only date but you are comparing date and time. in your case date is same but what about time.

by using datediff funcation and passing D (date) you compare only date not time and if date is same it will return  0 (zero) otherwise it will return some number.

Arif
• Marked as answer by Friday, January 15, 2010 1:19 PM
Friday, January 15, 2010 10:28 AM
• Testing using the following data produces the correct results for me:

```create table VU_HRG2 (CC_Start_Date_2 datetime, CC_Dis_Date_1 DATETIME, CCBDAy1 INT, CCBDay2 INT)

insert into VU_HRG2 SELECT '28 mar 2009', '28 mar 2009', 1, 1
insert into VU_HRG2 SELECT '8 jul 2009', '9 jul 2009', NULL, 1
insert into VU_HRG2 SELECT '6 may 2009', '6 may 2009', 1, 1

SELECT
CASE WHEN
(dbo.VU_HRG2.CC_Start_Date_2 = dbo.VU_HRG2.CC_Dis_Date_1) AND dbo.VU_HRG2.CCBDay1 is not NULL  THEN
'TRUE'
ELSE
'FALSE'
END AS CCBDay1
FROM         dbo.VU_HRG2
```

every day is a school day
• Marked as answer by Friday, January 15, 2010 1:19 PM
Friday, January 15, 2010 12:05 PM
• You should do datediff (dd, Date1, Date2) = 0 .

If you are not interested in the TIME part, you should use DATE data types in SQL Server 2008. That way, you can use the equal operator(=).

Can you test the following?

```DECLARE @StartDate datetime, @DischargeDate datetime
SET @StartDate = '2009-08-12 00:00:00.000'
SET @DischargeDate = '2009-08-12 00:00:00.000'

SELECT CASE
WHEN @StartDate = @DischargeDate THEN 'True'
ELSE 'False' END
GO
-- True```

Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
• Marked as answer by Friday, January 15, 2010 1:19 PM
Friday, January 15, 2010 12:05 PM

### All replies

• Is the datecolumn contains only date or datetime?? Did you check that?

Friday, January 15, 2010 9:45 AM

THE DATA TYPE OF DATE COLUMN IS DATETIME.

Friday, January 15, 2010 9:48 AM
• This the value 2009-03-28 00:00:00.000 and this value is exactly the same in both columns
Friday, January 15, 2010 9:51 AM
• Something like this?

```SELECT
CASE
WHEN  DATEDIFF(D,dbo.VU_HRG2.CC_Start_Date_2, dbo.VU_HRG2.CC_Dis_Date_1) = 0 AND dbo.VU_HRG2.CCBDay1 IS NOT NULL THEN
'TRUE'
ELSE
END AS CCBDay1
FROM         dbo.VU_HRG2
```

every day is a school day
• Proposed as answer by Friday, January 15, 2010 10:13 AM
• Marked as answer by Friday, January 15, 2010 1:20 PM
Friday, January 15, 2010 9:58 AM
• Try converting the datetime colum to a varchar as given belwo

Convert(VarChar(10), dbo.VU_HRG2.CC_Start_Date_2, 101) = Convert(VarChar(10), dbo.VU_HRG2.CC_Dis_Date_1, 101)
Friday, January 15, 2010 9:59 AM
• Thanks rich for your reply. I have to compare dates. If i will use datediff function then it will return me the numbers not date. numbers can be different. Why it is not comparing the date values? The data type of both date columns are DATETIME.

Yes i can use the DATEADD function (Thanks for this)to add the numbers thats is true but first it should meet the condition requirement and should come in the first part instead of else part.

Does it make sense? Please let me know if i m not explaining properly or u have any question.

Friday, January 15, 2010 10:05 AM
• Thanks Selva, no it is not working. what is 101? is it a format code?

Friday, January 15, 2010 10:18 AM
• (Cast And Convert)

http://msdn.microsoft.com/en-us/library/ms187928.aspx
Happy Coding, RDRaja
Friday, January 15, 2010 10:27 AM
• >>If i will use datediff function then it will return me the numbers not date. numbers can be different. Why it is not comparing the date values?

you want to compare only date but you are comparing date and time. in your case date is same but what about time.

by using datediff funcation and passing D (date) you compare only date not time and if date is same it will return  0 (zero) otherwise it will return some number.

Arif
• Marked as answer by Friday, January 15, 2010 1:19 PM
Friday, January 15, 2010 10:28 AM
• You have to be careful with datetime. It is not safe to assume that the time part is 00:00:00.000.

Can you post the results of the following queries?

```SELECT  VU_HRG2.CC_Start_Date_2,
VU_HRG2.CC_Dis_Date_1,
VU_HRG2.CCBDay1
FROM   dbo.VU_HRG2
GO

EXEC sp_help VU_HRG2
GO```

Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
Friday, January 15, 2010 10:47 AM
• Thanks SQLUSA for your reply. as requested these are the results.

 CC_Start_Date_2 CC_Dis_Date_1 CCBday1 2009-08-12 00:00:00.000 2009-08-12 00:00:00.000 1 2009-08-07 00:00:00.000 2009-08-07 00:00:00.000 NULL 2009-03-28 00:00:00.000 2009-03-28 00:00:00.000 1 2009-06-05 00:00:00.000 2009-06-05 00:00:00.000 1 2009-09-25 00:00:00.000 2009-09-25 00:00:00.000 3 2009-10-08 00:00:00.000 2009-10-08 00:00:00.000 2

Friday, January 15, 2010 11:04 AM
• What is @@version?

After removing the "dbo."  prefix everywhere, your query should work.

As everybody indicated above it is not best practices to use the equal operation (=) with datetime.
Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
Friday, January 15, 2010 11:25 AM
• Thanks SQLUSA. I am using SQL Server 2008 64 bit. i have reomved dbo as you said. query is running but it is going in else part. the problem is same. As you have seen the dates are same. It should go to the else part in the second record because CCBdays is null. For other records it should go in the first part of the case statement.

So what is the best practice to compare the date?
Friday, January 15, 2010 11:59 AM
• Testing using the following data produces the correct results for me:

```create table VU_HRG2 (CC_Start_Date_2 datetime, CC_Dis_Date_1 DATETIME, CCBDAy1 INT, CCBDay2 INT)

insert into VU_HRG2 SELECT '28 mar 2009', '28 mar 2009', 1, 1
insert into VU_HRG2 SELECT '8 jul 2009', '9 jul 2009', NULL, 1
insert into VU_HRG2 SELECT '6 may 2009', '6 may 2009', 1, 1

SELECT
CASE WHEN
(dbo.VU_HRG2.CC_Start_Date_2 = dbo.VU_HRG2.CC_Dis_Date_1) AND dbo.VU_HRG2.CCBDay1 is not NULL  THEN
'TRUE'
ELSE
'FALSE'
END AS CCBDay1
FROM         dbo.VU_HRG2
```

every day is a school day
• Marked as answer by Friday, January 15, 2010 1:19 PM
Friday, January 15, 2010 12:05 PM
• You should do datediff (dd, Date1, Date2) = 0 .

If you are not interested in the TIME part, you should use DATE data types in SQL Server 2008. That way, you can use the equal operator(=).

Can you test the following?

```DECLARE @StartDate datetime, @DischargeDate datetime
SET @StartDate = '2009-08-12 00:00:00.000'
SET @DischargeDate = '2009-08-12 00:00:00.000'

SELECT CASE
WHEN @StartDate = @DischargeDate THEN 'True'
ELSE 'False' END
GO
-- True```

Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
• Marked as answer by Friday, January 15, 2010 1:19 PM
Friday, January 15, 2010 12:05 PM
• Thanks SQLUSA, yes this code is returning TRUE. I cannot change the datatype becuase there is alot of data which i dont  want to change. Thank you so much for your help i will try this now.

Friday, January 15, 2010 12:22 PM
• Thanks SQLUSA  and all for your help. Could you please tell me how can subtract and add a int value from one int column?

Friday, January 15, 2010 2:02 PM

To subtract an integer from another integer, simply use

set @NewInt = @OneInt -@AnotherInt

And for the columns it would be the same

select One - Another as NewResult ....