I have a calculated column which is updated when two other columns are updated and the formula for this column is
SELECT(datediff(day,'2012-11-01 18:15:00.000','2012-10-22 11:59:58.000'))
It works fine for all other dates but when the month changes, then I get a negative value. How can I make sure this does not happen and I get the correct integer value?
Well, two options:
just make sure the earlier date is always the first in your list of dates for DATEDIFF
SELECT DATEDIFF(DAY, '2012-11-01 18:15:00.000', '2012-10-22 11:59:58.000')
This gives you a result of -10 - this is because the first date in your list is the later date.
If you use
SELECT DATEDIFF(DAY, '2012-10-22 11:59:58.000', '2012-11-01 18:15:00.000')
you get the result of 10 - since the first date in DATEDIFF is the earlier date.
just use ABS to get the absolute value (without the + or -):
SELECT ABS(DATEDIFF(DAY, '2012-11-01 18:15:00.000', '2012-10-22 11:59:58.000'))
In that case, you never get a negative value.