datetime running counter

Question

Post reply

datetime running counter

yves tortora

SSC Rookie

Points: 45
More actions
December 9, 2006 at 3:07 pm

#174048

Hi,

1) days counted from the "GETDATE" already inserted, and returning an integer in that column, automatically, all the time until after a specified number of days (say 20 for example), the relevant table row will be automatically deleted, replaced by another insert (row) to start again and this repeated many times.
and/or,

2) (GATEDATE()) running all the time in a column, to automatically always have the present date and time available in that column when opening the table.

One or the other will give something “ticking” continuously within the table column , initiated by an insert in the row , which automatically activate (getdate()).

Any ideas of how to? Cheers and thanks in advance.Yves.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

salim SSChasing Mays Points: 659 More actions · Answer 1

if you are using sql2005 then you can use DDL triggers.

read on

http://www.databasejournal.com/features/mssql/article.php/3581846

if you are using sql2000 then when ever a table is created then

insert into a special table the name of the table and getdate() ... like below

create table tablelist(tabname varchar(100),ts datetime)

go

create table test( ...column definition goes here ...);

insert tablelist select 'test',getdate();

-- note :: the string 'test' above should be replaced with the real table name

go

Also, if you take a look into sql2000 dictionary you will find a wealth of information there

SELECT TABLE_NAME, TABLE_TYPE 
  FROM INFORMATION_SCHEMA.TABLES

hope this quicky is helpful

El Guapo Old Hand Points: 356 More actions · Answer 2

If you are trying to have a column that has the current date/time whenever a new record is inserted, you can just have a column with a default value of getdate(). That way, unless you insert a different value, each time you insert a new row the current date will be inserted.

Len Bearse SSC Journeyman Points: 79 More actions · Answer 3

Salim,

There are several datetime functions that will provide you with the ability to count time in HH MM SS using quires if that what your trying to do. DatePart() and DateDiff() are functions that may help you.

I've tried with no success to create a simple method to simulate a stop watch counter in order to express elasp time. I was trying to determine time of a production line in DD:HH:MM:SS but the difficulty is when time elements exceed there limits IE Datediff( DD, 1/1/06 08:00 am, 1/2/06 8:00 PM) = 1 Day even with time datediff does not express the fraction of the date ie number of hours. What I needed was 01:12:00:00 or 1 day, 12 hours, 00 min, 00 sec. Exporting to MSExcel was my only solution. I would be interested on others comments and/or attempts.

Len

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_Salim]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[tbl_Salim]

GO

CREATE TABLE [dbo].[tbl_Salim] (

[Salim_id] [int] IDENTITY (1, 1) NOT NULL ,

[Inserted] [datetime] NULL ,

[str_text] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tbl_Salim] ADD

CONSTRAINT [DF_tbl_Salim_Inserted] DEFAULT (getdate()) FOR [Inserted]

GO

Insert tbl_Salim ( str_text)

select 'Test DateTime'

Select *

, getdate() as 'Getdate()'

, Datediff( DD, Inserted, getdate()) as ET_Days

, Datediff( HH, Inserted, getdate()) as ET_Hours

, Datediff( MI, Inserted, getdate()) as ET_Min

, Datediff( SS, Inserted, getdate()) as ET_Seconds

from tbl_Salim

Salim_id	Inserted	str_text	Getdate()	ET_Days	ET_Hours	ET_Min	ET_Seconds
1	12/11/06 11:47 AM	Test DateTime	12/11/06 12:48 PM	0	1	61	3657

(1 row(s) affected)

yves tortora SSC Rookie Points: 45 More actions · Answer 4

Hello Salim,EL Guapo,

Thanks for your interest in my topic "datetime running counter " and I appreciate your answer.I have changed the general wording of this topic, as I believe it does reflect better what I am after.

I have a database which works automatically.The data need not to be queried until the row is deleted.There is no manipulation of data.Once the data is entered in a row,the publication date is automatically set by (getdate()) until the entire row is deleted after a specified length of time,usually several days after the publication date.It is for this automatic deletion that it is necessary to "count 'the days from the publication date ,or to have at all time the present date and time in each row.

The publication date through (GETDATE()) is a "fixed" date which is OK for the publication date,

but I need (GETDATE()) to return the present date and time all the time,automatically,under the scene,running by itself,so when I open a table ,I can see each row has its own present date and time in a column.(also the publication date in another column).That is what I am after.

I believe this short text will enable you to "see" the topic from a better view.

Kind Regards and thanks.Yves.

MohammedU SSCoach Points: 19644 More actions · Answer 5

You can add no of days as calculated column..

See the following ex:

drop table #test

Create table #test (publicationdate datetime default getdate(), NoOfDays as datediff(dd, publicationdate, getdate() ))

insert into #test

select getdate()-1

insert into #test

select getdate()-2

select * from #test

MohammedU
Microsoft SQL Server MVP

salim SSChasing Mays Points: 659 More actions · Answer 6

I think Mohammed Uddin answer is the closest to your need, but,when a row is (physically deleted) it is gone , so it will not help unless you query data and manually look at data and check the difference in days/time then you manually delete the rows.

If that is what you want then you can use a delete statement that includes a check on how many days the record has been entered then delete those without building the extra column as it is an overhead if data table is big.

use something like

delete from MyTable

where datediff(Hour, publicationdate, getdate() )) > 72

this will delete rows having been entered more than 72 hours or 3 days ...and you do not have to bother about adding a new calculated column.

yves tortora SSC Rookie Points: 45 More actions · Answer 7

Query data and manually look at data and check the difference in days/time then to manually delete the rows,that is what precisely I do not want to do ,but set all that automatically working by itself,and including the row deletion,that is what I am after.

And yes ,when the row is deleted ,it is for good OK.

Regards.Yves.