Can you explain Converting and Subtracting Two Dates?
I'm trying to get the numeric difference between the two dates. (Ex: 11/30/2018 - 11/05/2018 = 25)
One of the dates (@EndOfMonth) represents the last day of the prior month and is being converted into a 'YYYY-MM-DD format
BEGIN DECLARE @EndOfMonth AS VARCHAR(10) SET @EndOfMonth = (SELECT CONVERT(VARCHAR, DATEADD(m, DATEDIFF(m, 0, DATEADD(m, 1, DATEADD(MM, -1, @maxDwdate))), -1), 112))
The second date that I'm looking to subtract from @EndOfMonth, is a field named "DUE DATE" and it's in an 'mm/dd/yy format.
This is fairly simple to achieve, the below example code shows how to do this (replace the DueDate variable with your DUE DATE column from your table. Basically, you use DATEDIFF to get the start of the month for DUE DATE, add one month to this date then subtract one day. This will always give you the EOM date for the DUE DATE month, regardless of 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)