Date diff Function giving negative values

_{Thursday, January 27, 2011 7:27 PM}

Hi i need days difference but i'm getting negative values even if the start date is recent than end date

startdate=2009-12-22 07:18:03.880                      end date=2004-01-01 00:00:00.000

DATEDIFF(DD, startdate, ISNULL(enddate, getdate()))         i was getting -2182

IS there any thing i need to fix the query pls let me know......

Thanks in Advance

_{Thursday, January 27, 2011 8:12 PM ✅Answered}

If you want the datediff to always return positive number regardless which date is later, then simply add ABS, e.g.

ABS(DATEDIFF(Day, BookedDate, ISNULL(RequestedDate, CURRENT_TIMESTAMP)) )-- I use GETDATE() myself, but I believe Celko it's proprietary.

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

_{Thursday, January 27, 2011 7:31 PM}

May be you need to correct your data first?

select * from YourTable where StartDate > EndDate -- if the Business Requirements mean the StartDate should be always less than EndDate (or EndDate can be NULL)

Correct all bad data and add a CHECK constraint to your table to avoid this problem in a future.

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

_{Thursday, January 27, 2011 7:35 PM}

The start date is after the end date so of course it will be negative.

Adam

Ctrl+Z

_{Thursday, January 27, 2011 8:05 PM | 1 vote}

Forgot about the startdate and enddate.............I just gave an alias names.

Ok can you tell me the solution for below code

bookeddate=2009-12-22 07:18:03.880                      requesteddate=2004-01-01 00:00:00.000

DATEDIFF(DD, bookeddate, ISNULL(requesteddate, getdate()))         i was getting -2182

_{Thursday, January 27, 2011 11:16 PM}

It may be worth pointing out that the arguments to datediff come in reverse order compared to regular subtraction. So the startdate normally comes first.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
(Just click the link you need.)