Date query Help.. - 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)

Date query Help..

Author

Topic

tooba
Posting Yak Master

224 Posts

Posted - 2012-11-26 : 14:08:53

Hi Guys,

I am not getting what i want from below sql statement.

SELECT * FROM sys.dm_exec_procedure_stats
WHERE TYPE = 'P'
AND last_execution_time > = DATEDIFF(m,-1,GETDATE())

Note:- I want to see only those store procedure that execute one month from now.

I am not getting what i need. Please guide me where i am wrong.

Thank You.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-26 : 14:17:35

Use DATEADD!

DATEADD(m,-1,GETDATE())

Jim

Everyday I learn something that somebody else already knew

tooba
Posting Yak Master

224 Posts

Posted - 2012-11-26 : 14:35:22

SELECT * FROM sys.dm_exec_procedure_stats
WHERE TYPE = 'P'
AND last_execution_time >= DATEADD(m,-1,GETDATE())

Below are result that i am getting....

last_execution_time
2012-11-26 11:31:42.183
2012-11-21 09:08:51.167
2012-11-26 11:33:08.107
2012-11-19 09:15:44.200
2012-11-26 11:32:03.273
2012-11-25 19:00:00.293
2012-11-16 12:48:15.340
2012-11-26 11:33:01.087
2012-11-16 11:39:02.753
2012-11-26 11:31:13.797
2012-11-21 15:34:19.863
2012-11-26 11:30:10.613
2012-11-20 14:55:38.713

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-26 : 15:03:28

That seems reasonable - all the dates are within one month of today. Isn't that what you are looking for?

tooba
Posting Yak Master

224 Posts

Posted - 2012-11-26 : 15:21:53

I need data one month old...

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-26 : 15:26:46

All the data you have is within the last month. Do tou mean data that is exactly one month old. To help you explain yourself better, if today is 2012-11-26 15:25:40.520, do you want records for just 2012-10-26?

Jim

Everyday I learn something that somebody else already knew

tooba
Posting Yak Master

224 Posts

Posted - 2012-11-26 : 16:05:13

Yes you are right jimf i want (if today is 2012-11-26 15:25:40.520, do you want records for just 2012-10-26)

Thank You.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-27 : 01:52:59

WHERE
CAST(last_execution_time AS DATE) = DATEADD( M, -1, CAST(getdate() AS DATE))

--
Chandu

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-27 : 06:38:59

WHERE last_execution_time >= dateadd(day,datediff(day,0,getdate()),0)
and last_execution_time < dateadd(day,datediff(day,-1,getdate()),0)

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources