Showing posts with label getdate. Show all posts
Showing posts with label getdate. Show all posts

Tuesday, March 27, 2012

Can't be rocket science to delete data older than 30 days?

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

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