Hello All
Here is the Question
I have created a table in order to time my query in parts:
For example
/*********************************************************************************/
USE [PUBS]
GO
CREATE TABLE [dbo].[IntegrationTestTime]
(
[Process] [varchar] (30),
[StartTime] datetime,
[EndTime] datetime,
[RunTime] datetime,
[DataRetrieveTime] datetime,
) ON [PRIMARY]
GO
INSERT INTO IntegrationTestTime (ProcessName) VALUES ('Change1');
INSERT INTO IntegrationTestTime (ProcessName) VALUES ('Change2')
/*********************************************************************************/
During the start of the query I am updating the StartTime Col for Change1 with Getdate()
UPDATE IntegrationTestTime SET StartTime = GETDATE() where Process = 'Change1'
At the End of the query i am updating EndTime Col for Change1 with Getdate()
UPDATE IntegrationTestTime SET EndTime = GETDATE() where Process = 'Change1'
Q1) I want to update RunTime Col with the time difference upto milli second from StartTime to EndTime col.
i.e, Runtime Col must have the exact time it took to execute the whole query
UPDATE IntegrationTestTime SET RunTime = (SELECT datediff(MS, StartTime, EndTime)
where Process = 'Change1')
But this gives me an error, i mean wrong value is being updated
Q2) In middle of the query, after executing certain SQL commands, I want to time it again and set [DataRetrieveTime] Colunm with the difference of StartTime and Getdate time at that instance.
Any suggestion please?
I would use a computed field, try this DDL for your table.
Code Snippet
CREATE TABLE [dbo].[IntegrationTestTime](
[Process] [nvarchar](50) NULL,
[StartTime] [datetime] NULL,
[EndTime] [datetime] NULL,
[RunTime] AS (datediff(millisecond,[StartTime],[EndTime])) PERSISTED,
[DataRetrieveTime] datetime
) ON [PRIMARY]
GO
|||Well lets see.
Check the data types of the columns and the values you are trying to store in them. You have defined RunTime as datetime and you are trying to store the milli seconds in them. To store the milli seconds you don't need a datetime column.
|||
I am want to store the time difference between the two time (Start and End Time). Its hould be upto an accuracy of milli second
Suppose Start tiem is 4:55:56.981
and ENd time is 5: 01:57.991
so the RunTime = 00: 06 : 01: 010
This is what i need
|||You need to understand how datetime works. Datetime uses 8 bytes to store the datetime and it uses first 4 bytes to store the date and next four to store the time. You can't separate the values. Your code is trying to store the milli seconds in a datetime field which tries to convert implicitly to a date and time value. If you want to store only the difference of time between columns, you need a int data type (only 4 bytes) not a datetime datatype. Also don't try to store the formats of data in the database. let app layer handle the formatting of data.|||
A could of things. 1. SQL Server will only give you .003 second accuracy in GetDate(). 2. Shawn's answer DOES store the difference, with no trigger or follow on code to muck with:
CREATE TABLE [dbo].[IntegrationTestTime](
[Process] [nvarchar](50) NULL,
[StartTime] [datetime] NULL,
[EndTime] [datetime] NULL,
[RunTimeMs] AS (datediff(millisecond,[StartTime],[EndTime])) PERSISTED,
) ON [PRIMARY]
GO
INSERT INTO IntegrationTestTime (Process) VALUES ('Change1');
INSERT INTO IntegrationTestTime (Process) VALUES ('Change2')
GO
UPDATE IntegrationTestTime SET StartTime = GETDATE() where Process = 'Change1'
UPDATE IntegrationTestTime SET EndTime = GETDATE() where Process = 'Change1'
select *
from IntegrationTestTime
Process StartTime EndTime RunTimeMs
-- -- -- --
Change1 2007-09-06 14:03:37.777 2007-09-06 14:04:06.157 28380
Change2 NULL NULL NULL
Your update statement:
UPDATE IntegrationTestTime SET RunTime = (SELECT datediff(MS, StartTime, EndTime)
WHERE Process = 'Change1')
Won't work because you are trying to store it to a datetime value. In my example, (after changing RunTime to be an integer) the value returns: 1925-08-21 00:00:00.000. So the problem is likely that the date being returned is out of the range for your tool?
|||I think i have a wrong code
Can any body suggest me whats the right code / procedure to get the difference between the Start and end.
I have started at so and so time
and ended at so and so time
whats the time taken to do taht job?
|||This is the right code: datediff(MS, StartTime, EndTime). It is just that it is not a date value. It is a number of milliseconds difference. You can turn this into 00:00:00.000 easily enough, just by doing the math to peel off minutes, seconds, etc. Here is a blog I wrote on that subject: http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!1238.entry
You could store that formatted output into your table, likely as a varchar value.
No comments:
Post a Comment