getdate day counter - 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

getdate day counter

Author

Topic

yves
Starting Member

5 Posts

Posted - 2006-12-13 : 23:36:01

Hello,

I need to count the days (24 hours)from the (GETDATE()),returning an integer in a table column, automatically, all the time , under the scene until after a specified number of days (usually 20 for example), the relevant table row is be automatically deleted, replaced by another insert (row) to start again and the process repeated many times.

I have a database which works automatically. The data need not to be queried or manipulated until the row is deleted automatically.
I use SQL Server 2000.

Any ideas of doing this in a simple way ?
Regards and thanks in advance.Yves.

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-12-14 : 04:52:01

Have a look at DATEDIFF in BOL. However, I've gotta ask why? What is it you're doing?

Mark

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-14 : 05:53:25

Can you post some sample data with expected result?

Madhivanan

Failing to plan is Planning to fail

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-12-14 : 09:52:11

Here are two different ways to get elapsed days.


select
	Elapsed_Days = datediff(ss,a.DT,getdate())/86400,
	Elapsed_Day = datediff(day,0,getdate()-a.DT)
from
	(select DT = convert(datetime,'20061201 12:35:35') ) a

Results:


Elapsed_Days Elapsed_Days 
------------ ------------ 
12           12

(1 row(s) affected)

CODO ERGO SUM

yves
Starting Member

5 Posts

Posted - 2006-12-15 : 17:32:31

quote:
Originally posted by madhivanan

Can you post some sample data with expected result?

Madhivanan

Failing to plan is Planning to fail

The data type of (getdate()) is datetime which is inserted automatically when the data is entered by a visitor through a web site, for example 1/12/2006 20:35:03. (SQL Server 2000 connected as ODBC to web site)

From that date, a number of days have been previously specified as part of the table design, say 8 for example.

In another column on the same table and same row, at the same time, the number 0 appears, since not one day yet has elapsed, then the number 1 appears after one day (24 hours) has elapsed in real time, replaced by 2 for the second day , 3 etc.. as the real time goes on, until the number 8 appears, at which the row as previously part of the design, is automatically deleted including the data.

The data type is mainly nvarchar and image is optional.

I hope this explain better the subject of what I try to do.
Regards. Thanks, Yves.

yves
Starting Member

5 Posts

Posted - 2006-12-15 : 17:46:51

quote:
Originally posted by mwjdavidson

Have a look at DATEDIFF in BOL. However, I've gotta ask why? What is it you're doing?

Mark

Please see the note above.Cheers , Yves.

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2006-12-16 : 20:41:28

Yves,

You don't need to store the elapsed time...

DELETE yourtable
WHERE GETDATE()-yourdatecolumn > 8

There are two things about this that you should know... you should never delete... archive instead. The other thing is that NOTHING happens in SQL Server automatically... you either need a scheduled job, a trigger, or someone to run a proc themselves. You could add a trigger to the table that says when ever someone does an insert, to run the code I posted above if you really want to delete instead of archiving.

Keep in mind, that someone must actually do an insert, update, or delete to get the trigger to fire.

--Jeff Moden

yves
Starting Member

5 Posts

Posted - 2006-12-17 : 17:50:21

quote:
Originally posted by Michael Valentine Jones

Here are two different ways to get elapsed days.

select
Elapsed_Days = datediff(ss,a.DT,getdate())/86400,
Elapsed_Day = datediff(day,0,getdate()-a.DT)
from
(select DT = convert(datetime,'20061201 12:35:35') ) a

Results:

Elapsed_Days Elapsed_Days
------------ ------------
12 12

(1 row(s) affected)

CODO ERGO SUM

Thanks for the infos.I note it and will see that. Yves.

yves
Starting Member

5 Posts

Posted - 2006-12-17 : 18:01:41

quote:
Originally posted by Jeff Moden

Yves,

You don't need to store the elapsed time...

DELETE yourtable
WHERE GETDATE()-yourdatecolumn > 8

There are two things about this that you should know... you should never delete... archive instead. The other thing is that NOTHING happens in SQL Server automatically... you either need a scheduled job, a trigger, or someone to run a proc themselves. You could add a trigger to the table that says when ever someone does an insert, to run the code I posted above if you really want to delete instead of archiving.

Keep in mind, that someone must actually do an insert, update, or delete to get the trigger to fire.

--Jeff Moden

Hello Jeff,
Thanks for the lead, appreciated. I will take it into account in trying to achieve what I am after in this topic.
Cheers, Yves.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-18 : 09:23:53

More on Datetime
www.sql-server-performance.com/fk_datetime.asp

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-21 : 02:33:25

quote:
Originally posted by Jeff Moden

DELETE yourtable
WHERE GETDATE()-yourdatecolumn > 8

I prefer using

DELETE yourtable
WHERE yourdatecolumn <DateAdd(day,Datediff(day,0,Getdate()),-8)

as it can make use of index if it is defined on the Date column

I beleive that the execution plan of GETDATE()-yourdatecolumn > 8
will be same as that of DATEDIFF(day,yourdatecolumn,Getdate())>8

Madhivanan

Failing to plan is Planning to fail

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2006-12-24 : 01:03:42

Yep... not enough coffee...

--Jeff Moden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-24 : 23:04:59