| Author |
 Topic  |
|
|
tooba
Posting Yak Master
107 Posts |
 Posted - 11/26/2012 : 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
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 11/26/2012 : 14:17:35
|
Use DATEADD!
DATEADD(m,-1,GETDATE())
Jim
Everyday I learn something that somebody else already knew |
 |
|
|
tooba
Posting Yak Master
107 Posts |
Posted - 11/26/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/26/2012 : 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
107 Posts |
Posted - 11/26/2012 : 15:21:53
|
| I need data one month old... |
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 11/26/2012 : 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
107 Posts |
Posted - 11/26/2012 : 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
Flowing Fount of Yak Knowledge
India
1451 Posts |
Posted - 11/27/2012 : 01:52:59
|
WHERE
CAST(last_execution_time AS DATE) = DATEADD( M, -1, CAST(getdate() AS DATE))
--
Chandu |
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 11/27/2012 : 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 |
|
|
| |
Topic |
|
Date query Help..
