You can use the DATEADD() function to subtract dates or times in SQL Server. It takes three arguments. The first argument is the date/time unit – in our example, we specify the day unit. Next is the date or time unit value.
This is fairly simple to achieve, the below example code shows how to do this (replace the DueDate variable with your DUEDATE column from your table. Basically, you use DATEDIFF to get the start of the month for DUEDATE, add one month to this date then substract one day. This will always give you the EoM date for the DUEDATE month, regardless fo the number of days in that month. You can then simply do a DATEDIFF to work out the number of days between the two dates. SQL Server will implicitly convert yyyymmdd into a DATETIME value for the calculation, but if it's possible, you should avoid converting the EndOfMonth variable to a string for performance reasons.
DECLARE @EndOfMonth VARCHAR(10), @DueDate DATETIME = '12/14/2018' SELECT @EndOfMonth = CONVERT(VARCHAR(10), DATEADD(d, -1, DATEADD(m, 1, DATEADD(m, DATEDIFF(m, 0, @DueDate), 0))), 112) SELECT DATEDIFF(d, @DueDate, @EndOfMonth)
