# SSRS: Turning a Date Value into an Integer for a Mathematical Calculation

Hello:

At the end of this posting is a snippet of code from my larger query.  There are two fields, here:  "Total" and "Month".

I want to leave Month the way that it is, but I need to change the Total field.  Specifically, I need to multiply what I show now as Total, by an integer.  This integer is based on a timeframe.

Specifically, the RCG.dbo.ContractHeader table has StartDate and EndDate fields.  I need to subtract StartDate from EndDate and, then, subtract GetDate().  This should give me an integer representing number of months.

It's this number of months that I will multiply times the Total field's product of AvailQuantity and UNITCOST (from the code below).  This will ultimately give me the value of the amount of time remaining in the contract!

Since StartDate and EndDate can represent months that cut across years, how do I derive a number of months and do what I just said I need:  (EndDate-StartDate)-GetDate()?

Thanks!

TBSupport

SELECT
(RCG.dbo.POContractDetail.AvailQuantity)*(RCG.dbo.POContractDetail.UNITCOST) AS Total,
'Month ' + CASE WHEN DATEDIFF(m,GETDATE(),RCG.dbo.POContractHeader.EndDate) + 1 < 10 THEN CAST(CAST('0' AS VARCHAR) + CAST(DATEDIFF(m,GETDATE(),RCG.dbo.POContractHeader.EndDate) + 1 AS VARCHAR(1)) AS VARCHAR(2))
ELSE CAST(DATEDIFF(m,GETDATE(), RCG.dbo.POContractHeader.EndDate) + 1 AS VARCHAR(2))  END AS Month,
LVL 1
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Hi TB,

It sounds like what would work is getting the difference between start and end date using DATEDIFF like:

DATEDIFF(M, <YourStartDate>, <YourEndDate>)    -- if you reverse the order your number will be negative..

Then.. do the difference between your End Date and GETDATE() like:

DATEDIFF(M, <YourEndDate>, GETDATE())

``````SELECT
DATEDIFF(M, <YourStartDate>, <YourEndDate>) As Start_Diff
,   DATEDIFF(M, <YourEndDate>, GETDATE()) As End_Diff
,  (   DATEDIFF(M, <YourStartDate>, <YourEndDate>)    )   +   (DATEDIFF(M, <YourEndDate>, GETDATE())   )   As Total_Diff

FROM <YourTable>
``````

Then you could apply that in your CASE statement - if that isn't what you're looking for please advise.
0
Author Commented:
I got a negative figure for the total field and my data is duplicating.
0
Commented:
Hi TB,

Did you put the ENDATE before the STARTDATE in your DATEDIFF statement?  Assuming that your startdate preceeds your enddate, and your enddate is not in the future (as in an estimated enddate for a contract), then should only be able to produce a positive number if you put the earlier date ahead of the later date in the DATEDIFF statement.  Can you copy what you ended up using?
0
Author Commented:
The end date is, indeed, in the future.  Again, that's why I said that the Start Date and End Date fields can cross into years and, specifically, future years.
0
Author Commented:

For now, I have put this aside.

--(RCG.dbo.POContractDetail.AvailQuantity)*(RCG.dbo.POContractDetail.UNITCOST) AS Total,
*(RCG.dbo.POContractDetail.AvailQuantity)*(RCG.dbo.POContractDetail.UNITCOST) AS Total,
'Month ' +
CASE WHEN DATEDIFF(m,GETDATE(),RCG.dbo.POContractHeader.EndDate) + 1 < 10
THEN CAST(CAST('0' AS VARCHAR) + CAST(DATEDIFF(m,GETDATE(),RCG.dbo.POContractHeader.EndDate) + 1 AS VARCHAR(1)) AS VARCHAR(2))
ELSE CAST(DATEDIFF(m,GETDATE(), RCG.dbo.POContractHeader.EndDate) + 1 AS VARCHAR(2))
END
AS Month, 0 as PastDue
RCG.dbo.POContractDetail.ContractNum LEFT OUTER JOIN
RCG.dbo.PODraws.ContractNum
HAVING (RCG.dbo.POContractDetail.AvailQuantity)*(RCG.dbo.POContractDetail.UNITCOST) > 0
0
Commented:
Hi TB,

If I understand correctly, and your objective is to determine how many months remain in the contract.  If that's the case then if startdate is in the past and enddate is in the future, we don't need to use startdate at all, we just want the difference between GETDATE() and the endate of the contract.  In cases where contracts END in the past you probably want to see zero (0) -- assuming that's even possible in your data set.  Lastly, if the startdate is also in the future, then naturally so is the enddate, and I assume you want to see an integer representing the total length of the contract (as opposed to the difference between the end of the contract and the current date..)

Assuming I'm understanding I would use logic like the following:

`````` CASE
WHEN [yourstartdate] > GETDATE()
THEN DATEDIFF(M, [yourstartdate], [yourenddate])
WHEN [yourenddate]<= GETDATE()
THEN 0
ELSE DATEDIFF(M, GETDATE(), [yourenddate])
END As 'Months_Remaining'
``````

0
Author Commented:
Hi CoreConcepts:

You're correct, per the first statement that you made:   "my objective is to determine how many months remain in the contract.  If that's the case then if startdate is in the past and enddate is in the future, we don't need to use startdate at all, we just want the difference between GETDATE() and the endate of the contract."

That being the case, does that change the code that you suggested, in your recent posting?

TBSupport
0
Commented:
Hi TB,

Looks like we're on the same page - no that won't change the logic (assuming we considered the scenarios possible in your dataset).

I tossed in that logic in your code block and commented out what you had for the Total column and replaced it with your specific fields.  It looked good to me, but as I can't run it I'll have to rely on you to tell me.

Code follows:

``````SELECT  RCG.dbo.POContractHeader.VENDORID as VendorID,
--(RCG.dbo.POContractDetail.AvailQuantity)*(RCG.dbo.POContractDetail.UNITCOST) AS Total,
--*(RCG.dbo.POContractDetail.AvailQuantity)*(RCG.dbo.POContractDetail.UNITCOST) AS Total,
(CASE
THEN 0
END)*(RCG.dbo.POContractDetail.AvailQuantity)*(RCG.dbo.POContractDetail.UNITCOST) As Total,
'Month ' +
CASE WHEN DATEDIFF(m,GETDATE(),RCG.dbo.POContractHeader.EndDate) + 1 < 10
THEN CAST(CAST('0' AS VARCHAR) + CAST(DATEDIFF(m,GETDATE(),RCG.dbo.POContractHeader.EndDate) + 1 AS VARCHAR(1)) AS VARCHAR(2))
ELSE CAST(DATEDIFF(m,GETDATE(), RCG.dbo.POContractHeader.EndDate) + 1 AS VARCHAR(2))
END
AS Month, 0 as PastDue
RCG.dbo.POContractDetail.ContractNum LEFT OUTER JOIN
RCG.dbo.PODraws.ContractNum
HAVING (RCG.dbo.POContractDetail.AvailQuantity)*(RCG.dbo.POContractDetail.UNITCOST) > 0
``````
0

Experts Exchange Solution brought to you by