ABS and DATEDIFF - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

ABS and DATEDIFF

Author

Topic

dmilam
Posting Yak Master

185 Posts

Posted - 2012-10-26 : 14:32:45

My understanding is that ABS returns positive values, but when I pass a DATEDIFF call to it, it returns negative ones as well.

When I say it helps, I'm using it to bind the DATEDIFF values to within a range of 365 days or less, whereas without ABS, the signed integers returned are far out of this range.

I realize these are a few questions but I can't find an explanation anywhere. Perhaps I don't understand DATEDIFF either.

Assuming here that SELECT will output what is bound by WHERE.


-- returns 364 to -2916893
SELECT DISTINCT DATEDIFF(Day,a.ImportantDate1,CURRENT_TIMESTAMP)
FROM dbo.Table1 a
INNER JOIN dbo.Table2 b ON a.ID = b.PersonID
WHERE DATEDIFF(Day,a.ImportantDate1,CURRENT_TIMESTAMP) <= 365


-- returns 364 to -340
SELECT DISTINCT DATEDIFF(Day,a.ImportantDate1,CURRENT_TIMESTAMP)
FROM dbo.Table1 a
INNER JOIN dbo.Table2 b ON a.ID = b.PersonID
WHERE ABS(DATEDIFF(Day,a.ImportantDate1,CURRENT_TIMESTAMP)) <= 365

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-26 : 14:54:51

I don't know why your datediff function returns -2916893. That doesn't seem possible. Can you post the a.ImportantDate1 value from the row that generated -2916893?

When you use ABS, it is behaving correclty. It finds the difference in dates, and then takes the absolute value. The absolute value would be 0 to 365 for all datediff values from -365 to +365. That is why you see the -364 in the results.

For you to get dates between 0 and 365, use the following:

WHERE DATEDIFF(Day,a.ImportantDate1,CURRENT_TIMESTAMP) BETWEEN 0 AND 365

For performance reasons, it might be better to change the query like this:

WHERE a.ImportantDate1 
	BETWEEN DATEADD(dd,DATEDIFF(dd,0,CURRENT_TIMESTAMP),0)
	AND DATEADD(dd,DATEDIFF(dd,0,CURRENT_TIMESTAMP),365)

dmilam
Posting Yak Master

185 Posts

Posted - 2012-10-26 : 16:17:06

Thanks very much. The row value associated with -2916893 is 9999-01-01 00:00:00.000.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources