DateDiff Question... - 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

General SQL Server Forums

New to SQL Server Programming

DateDiff Question...

Author

Topic

munkdogg
Yak Posting Veteran

53 Posts

Posted - 2008-03-13 : 12:34:13

I am trying to determine the number of records modified during the last 24 hours, using the DatDiff function. My script is as follows;

select count (*) from dbo.table1
where DateDiff (day,column1,GetDate())> 0

This is returning WAY more records than I thought...almost as many as there are in the entire table actually.

Can someone confirm if this logic makes sense, or can otherwise recommend a method of capturing the number of records modified in XX number of hours?

Many thanks..

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-03-13 : 12:47:10

That will give you just about all the records in the table. As column1 goes back in time the datediff gets bigger and bigger. Do you literally want the last 24 hours (from 12:46 yesterday to 12:46 today), or just everything from yesterday?

Jim

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-13 : 12:57:06

[code]-- All records for the last 24 hours
SELECT
COUNT(*)
FROM
dbo.table1
WHERE
Column1 >= GETDATE() - 1

-- All Records for Yesterday
SELECT
COUNT(*)
FROM
dbo.table1
WHERE
Column1 >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) - 1
AND Columm1 < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)

-- EDIT - forgot you wanted to do this with datediff
-- All records for the last 24 hours -- Using DATEDIFF
SELECT
COUNT(*)
FROM
dbo.table1
WHERE
Column1 >= DATEDIFF(DAY, -1, GETDATE())[/code]

munkdogg
Yak Posting Veteran

53 Posts

Posted - 2008-03-13 : 14:41:33

Ahhhh...now I see where the script went wrong. Many thanks to you both!

One more question - is there a way to pass hours via the DateDiff function? ie; everything modified during the last 60 minutes?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-03-13 : 15:33:59

Yes, DATEDIFF does all parts of the Datetime (years,hours, minutes, etc.)
Books On Line will have all data on that

SELECT DATEADD(minute,-60,current_timestamp) = 60 minutes ago

Jim

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-14 : 02:12:43

An article that describes how DATEDIFF works can be found here
http://www.sqlteam.com/article/datediff-function-demystified

E 12°55'05.25"
N 56°04'39.16"

munkdogg
Yak Posting Veteran

53 Posts

Posted - 2008-03-14 : 09:59:10

Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources