• ### Question

• Hey,

I have to columns with a fromdate and todate. The fromdate = 01-01-2015 and the todate = '01-01-9999' and I like to count the number of days between those to dates, but if the todate > today then it have to use the today date instead. Is that possible? I dont want to use a second column.

• Edited by Wednesday, May 20, 2015 9:23 AM
Wednesday, May 20, 2015 9:22 AM

• Hi Daniel,

You can use a combination of the TODAY and INT functions to achieve this.

If you want to create this as a Calculated Column then you can try something like this:

```=
IF(
'TableName'[todate] > TODAY(),
INT(TODAY()) - INT('TableName'[fromdate]),
INT('TableName'[todate]) - INT('TableName'[fromdate])
)```

If you want to create a Calculated Field then you can try something like this:

```SumOfDifferenceInDays:=
SUMX(
'TableName',
IF(
'TableName'[todate] > TODAY(),
INT(TODAY()) - INT('TableName'[fromdate]),
INT('TableName'[todate]) - INT('TableName'[fromdate])
)
)```

Regards,

Website: http://www.nimblelearn.com, Twitter: @nimblelearn

• Marked as answer by Wednesday, May 20, 2015 12:48 PM
Wednesday, May 20, 2015 12:22 PM

### All replies

• Hi Daniel,

You can use a combination of the TODAY and INT functions to achieve this.

If you want to create this as a Calculated Column then you can try something like this:

```=
IF(
'TableName'[todate] > TODAY(),
INT(TODAY()) - INT('TableName'[fromdate]),
INT('TableName'[todate]) - INT('TableName'[fromdate])
)```

If you want to create a Calculated Field then you can try something like this:

```SumOfDifferenceInDays:=
SUMX(
'TableName',
IF(
'TableName'[todate] > TODAY(),
INT(TODAY()) - INT('TableName'[fromdate]),
INT('TableName'[todate]) - INT('TableName'[fromdate])
)
)```

Regards,

Website: http://www.nimblelearn.com, Twitter: @nimblelearn

• Marked as answer by Wednesday, May 20, 2015 12:48 PM
Wednesday, May 20, 2015 12:22 PM
• Beautiful - thank you so much.
Wednesday, May 20, 2015 12:49 PM
• ```DECLARE @YourTable TABLE (
fromdate date,
todate date
)
INSERT INTO @YourTable VALUES
('01-01-2015', '01-01-9999')

SELECT
fromdate
, todate
, CASE
WHEN DATEDIFF(DAY, GETDATE(), todate) > 0 THEN DATEDIFF(DAY, fromdate, GETDATE())
ELSE DATEDIFF(DAY, fromdate, todate)
END AS NumberOfDays
FROM @YourTable```

A Fan of SSIS, SSRS and SSAS

Wednesday, May 20, 2015 12:50 PM
• Glad I could help :)

Regards,