360-Day Calendar Year Calculation mimicking excel DAYS360

Other SQL Server Topics
1

Found Discussion about and function named DATEDIFF360 here: TOPIC_ID=105375 in the old forum
Checked it, has issues.
Did it in plain T-SQL, as primitivly as possible...

Note that below code does not aim at constituting any new way of calculating a 360-Day-Year but at mimicking the behaviour of excel's DAYS360 Function, but omitting the inconsistencies
(As the 28th of Feb is a special case, below code-example uses that day)

SELECT
*
,(MonthsBetweenStartAndEndMonth+AddFullStartMonth+AddFullEndMonth)*30+AddDaysOfStartMonth+AddDaysOfEndMonth as DateDiff360
FROM
(
select
'2022-02-28' as StartDay
,'2022-06-30' as EndDay
,DATEDIFF(MONTH, '2022-02-28', '2022-06-30') as MonthDiff
, DATEDIFF(MONTH, '2022-02-28', '2022-06-30') -1 as MonthsBetweenStartAndEndMonth
, CASE WHEN DAY('2022-02-28') = 1 THEN 1 ELSE 0 END as AddFullStartMonth
, CASE WHEN DAY('2022-06-30') = DAY(Eomonth('2022-06-30')) THEN 1 ELSE 0 END as AddFullEndMonth
,CASE WHEN DAY('2022-02-28') = 1 THEN 0
ELSE
30-DAY('2022-02-28')
--> excel fails if EndDay is 28th of Feb and on some other occasions
--> optional: add 1 day to INCLUDE both Start and End Day in calc: 31-DAY('2022-02-28')
END as AddDaysOfStartMonth
,CASE WHEN DAY('2022-06-30') = DAY(Eomonth('2022-06-30')) THEN 0 ELSE DAY('2022-06-30') END as AddDaysOfEndMonth
) as base

2

Have you tried this?

Calculating 30/360 day count convention | sqlsunday.com

3

Thx Rogier - not before you pointed at it. Ty for that, works real well.
And, as mentioned in the article on sqlsunday, this solution seems to truly work nearly as excel does - except for some inconsistencies of excel which it does not copy.
So, it makes no sense for me to go on trying to get same results as excel, because I simply need to get it right, and replace the excel-file with its inconsistencies by T-SQL. That goal is now reached, and the last edge cases clarified. Edge cases depending on intention of date values entered, not on conventions of how to calculate 360 ... especially when a contract starts on Feb,28, and ends on Feb 27 next year. User intention = 360 ... convention is not :slight_smile:

1 Like