Click here to Skip to main content
15,068,929 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi everyone,

I am using sql server 2005. On select query I make difference between dates. But for some rowa it is giving -ve values. how this problem be solved? Below is the example for -ve value

DATEDIFF(day,contractexpirydate,entrydate) DaysLeftForContractExpiry

where contractexpirydate=01/06/2010
and entrydate = 08/14/2002
result : -2702

Updated 18-May-10 0:51am

Change the values round. The format of DATEDIFF expects the start date before the end date, so you need to change it to:
DATEDIFF(day, entrydate, contractexpirydate) DaysLeftForContractExpiry
The syntax for DATEDIFF is DATEDIFF ( datepart , startdate , enddate )
where the date difference is calculated as (enddate - startdate).

As a result you are getting a negative result in the above query.
Change the order of the two columns in your query to fix this.

If you get other negative values, it basically means your contractexpiredate is less than your entrydate and your business logic should handle such conditions appropriately.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900