Insert current date into column

[tmf]

Hello,

Is there a way to write a job that would insert the current date into a column from a table?

I am attempting to create a KPI (Key Perfmance Indicator) for Management through the use of SharePoint 2007 and one of the indicator field is the difference between the date a help desk call was entered into the db and today's date.

What I would like to accomplish is to have a background job run at Midnight that would data in the TodaysDate column with the current date so that the KPI would always reflect a current position on the record.

Thank you for your thoughts.

Sincerely,
Tim

 

[bborissov]

Check GETDATE() function in BOL, You could make a default value of the column to be GETDATE() and don't include that field in your INSERT statement.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[gmmastros]

Instead of storing today's current date in the database, why can't you use GetDate() in the query that returns the data? This way, you won't need to use the storage space for each record, and you won't have to re-populate the data every day.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[tmf]

Thank you both for your comments. I can't beleive that I didn't think of that (since I have used this in the past in previous code). I think I need to leave early today...

Kind Regards,

Tim

 

[tmf]

Oh! Now I remember why I wanted to have that date. My delema was that if I coded the TodaysDate dynamically obtaining it through the query code then I wouldn't be able to store the historical data in reference to the number of days an incident was opened.

The way the application is setup is that a user can post a help desk incident and during that time the datetime for the record is stamped for the creation date. In the db there are two other columns of importance (TodaysDate and DaysOpened). I wanted to be able to store the TodaysDate dynamically so that I could also maintain the historical data for number of days opened. Once the incident is closed, it would still be possible to view how many days this incident took to close.

Does this make sense?

Thank you agin for your help.

Tim

 

[tmf]

Ok, I was able to get the TodaysDate to populate when a new record is added with the current date using the default value or binding field in the columns properties.

However, what I need is to have that date change daily until the issue is closed. Thus I was thinking that I would need to script a SQL job to run nightly that would modify that date bsaed upon the Status field.

Continuing...

Thanks for your input.

Tim

 

[gmmastros]

Let me see if I understand correctly.

When a help desk incident is initiated, you create a record in the table. Currently, there is a CreationDate field that is automatically populated using GetDate().

When an incident is closed, you set the DaysOpened to the number of days it took to close the incident.

My question to you is... what is the value of DaysOpened for the duration of the incident? Is it NULL?

If you have NULL in the field until the incident is closed, you could modify the query to be...

[tt][blue]
Coalesce(DaysOpened, DateDiff(Day, CreationDate, GetDate()) As DaysOpened,
[/blue][/tt]


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[tmf]

George,

Almost. The way the application is setup is that there are 3 columns dealing with the duration of an incident:
DateCreated - datetime
TodaysDate - datetime
DaysOpen - numeric

What I am trying to accomplish is to have the database conduct some sort of refresh daily that will allow the todaysdate to reflect the current date and not just the date the record was either entered or edited.

So what I was woring on was using a job routine that would run nightly to refresh the todaysdate column for all entered records and recalculate the number of days (diff between todaysdate and datecreated).

Sorry for the confusion. This is evolving by the minute as I discover other ways of accomplishing this task.

Please feel free to make any suggestions you deem to improve my end result.

Kind regards,
Tim