How to Convert and Subtract Two Dates in SQL server?
I'm trying to get the numeric difference between two dates. (Ex: 11/30/2018 - 11/05/2018 = 25)
One of the dates (@EndOfMonth) represent the last day of the prior month and is being converted into a 'yyyymmdd' 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 "DUEDATE" and it's in a 'mm/dd/yyyy' format. Can sql server subtract dates?
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)