Can it ?
is it this simple? Or is it supposed to > ?
DELETE FROM LOGRECS
WHERE datediff(d,getdate(), time)<=30Try:
DELETE FROM tbl
WHERE time_col <= DATEADD( d, -30, CURRENT_TIMESTAMP ) ;
Anith|||You're saying within the last 30 days. This statement might be more logical
to follow:
DELETE LogRecs
WHERE [time] < GETDATE() - 30;
or
DELETE LogRecs
WHERE [time] < DATEADD(DAY, -30, GETDATE());
"Kevini" <Kevini@.discussions.microsoft.com> wrote in message
news:5882D56F-7395-46A4-A49E-D0B096A47D8F@.microsoft.com...
> Can it ?
> is it this simple? Or is it supposed to > ?
> DELETE FROM LOGRECS
> WHERE datediff(d,getdate(), time)<=30|||Anith Sen wrote:
> Try:
> DELETE FROM tbl
> WHERE time_col <= DATEADD( d, -30, CURRENT_TIMESTAMP ) ;
> --
> Anith
When in doubt, don't delete.
SELECT MAX(time_col)
-- DELETE
FROM tbl
WHERE time_col <= DATEADD( d, -30, CURRENT_TIMESTAMP ) ;
Measure twice, cut once.|||I only want the last 30 days so nothing earlier than May 29 or should I say
records form today and back 30 days
"Aaron Bertrand [SQL Server MVP]" wrote:
> You're saying within the last 30 days. This statement might be more logic
al
> to follow:
> DELETE LogRecs
> WHERE [time] < GETDATE() - 30;
> or
> DELETE LogRecs
> WHERE [time] < DATEADD(DAY, -30, GETDATE());
>
>
> "Kevini" <Kevini@.discussions.microsoft.com> wrote in message
> news:5882D56F-7395-46A4-A49E-D0B096A47D8F@.microsoft.com...
>
>|||Kevini wrote:
> Can it ?
> is it this simple? Or is it supposed to > ?
> DELETE FROM LOGRECS
> WHERE datediff(d,getdate(), time)<=30
It helps to have the rocket pointed in the right direction... :-)
WHERE DATEDIFF(d, time, GETDATE()) > 30|||Thanks Guys...Aaron's worked beautifully. And the rocket pointed the right
way:-)
"Tracy McKibben" wrote:
> Kevini wrote:
> It helps to have the rocket pointed in the right direction... :-)
> WHERE DATEDIFF(d, time, GETDATE()) > 30
>|||Kevini wrote:
> Thanks Guys...Aaron's worked beautifully. And the rocket pointed the right
> way:-)
>
Houston, we have lift-off!|||>I only want the last 30 days
But your subject says "data older than 30 days"
If you want rows that are within the last 30 days, then
WHERE [time] > DATEADD(DAY, -30, GETDATE())
AND [time] <= GETDATE()|||Kevini wrote:
> Thanks Guys...Aaron's worked beautifully. And the rocket pointed the right
> way:-)
>
So, is it really a rocket, or are you just happy to see us ;)
/impslayer, aka Birger Johansson
Showing posts with label getdate. Show all posts
Showing posts with label getdate. Show all posts
Tuesday, March 27, 2012
Sunday, March 11, 2012
Cannot use GETDATE ( ) in a function?
I want to use GETDATE ( ) in a user defined function for date comparing. When I compile the function, got the error:
Invalid use of 'getdate' within a function.
How to get the current date in a function? Any suggestion will be appreciated.
ZYTThe way that SQL 2000 impliments User Defined Functions (UDFs) requires that the functions be deterministic, in other words that for a given set of function arguments the UDF will always return the same result. GetDate() is non-deterministic because it should return a different value every time it is called. Because of that, you can't call GetDate() within a SQL 2000 UDF, although you can use GetDate() as an argument to one and use that argument any way you see fit.
-PatP|||you cannot use a non-deterministic function within a UDF.getdate() is a non deterministic function. So better pass getdate() value as parameter to ur function .There are otherways too.check this link for more details.
http://www.aspfaq.com/show.asp?id=2439
Invalid use of 'getdate' within a function.
How to get the current date in a function? Any suggestion will be appreciated.
ZYTThe way that SQL 2000 impliments User Defined Functions (UDFs) requires that the functions be deterministic, in other words that for a given set of function arguments the UDF will always return the same result. GetDate() is non-deterministic because it should return a different value every time it is called. Because of that, you can't call GetDate() within a SQL 2000 UDF, although you can use GetDate() as an argument to one and use that argument any way you see fit.
-PatP|||you cannot use a non-deterministic function within a UDF.getdate() is a non deterministic function. So better pass getdate() value as parameter to ur function .There are otherways too.check this link for more details.
http://www.aspfaq.com/show.asp?id=2439
Subscribe to:
Posts (Atom)