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

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.

  • The proper DATE data type does not have a "format", not until you convert it to a string. So, stop converting DATE. – mustaccio Dec 13 '18 at 22:35

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 its possible, you should avoid converting the EndOfMonth variable to a string for performance reasons.

    @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)
  • That seemed to be the winning ticket, THANK YOU KINDLY – Mike Jones Dec 13 '18 at 23:49

Assuming that you have a valid reason to convert the date to a string, and that you cannot change that logic: declare yourself a variable to store the intermediate results of the date arithmetic before you convert it to a string, then you can easily get the difference between the two dates:

      = DATEADD(m, DATEDIFF(m, 0, DATEADD(m, 1, DATEADD(MM, -1, @maxDwdate))), -1);
    SET @EndOfMonth = (SELECT CONVERT(VARCHAR, @eom, 112))
   FROM t; -- replace with actual table name.
  • A little rough but I was able to get it to work – Mike Jones Dec 13 '18 at 23:48
  • 1
    Thanks for the feedback. Which part was there a problem with? – Mr.Brownstone Dec 13 '18 at 23:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.