?
Solved

Substracting starttime and endtime in Stored Procedure with Transaction

Posted on 2011-05-04
16
Medium Priority
?
468 Views
Last Modified: 2012-05-11
the following stored procedure will firstly insert record, get the new id and update record with the id,

however, it doesnt have substracting in the sp,
currently using dtStarttime for timetaken parameter for compiling it,

looking for correct way of substracting the starttime and endtime,
and finally place in the timetaken.
CREATE PROCEDURE [dbo].[InsertAndUpdateTimeTaken]


@dtStartTime datetime,
@dtEndTime datetime,
@strAsgnmt varchar(max)

AS
BEGIN TRY

BEGIN TRANSACTION TransInsertAndUpdateTimeTaken
DECLARE @id int; 
Insert into dbo.DailyITSupportAssignment(timestart, timeend, assignmentname) VALUES (@dtStartTime, @dtEndTime, @strAsgnmt) 
set @id = SCOPE_IDENTITY(); 

UPDATE dbo.DailyITSupportAssignment SET timetaken = @dtStartTime WHERE studentid = @id;

COMMIT TRANSACTION TransInsertAndUpdateTimeTaken 

END TRY

BEGIN CATCH
         if @@TRANCOUNT > 0
ROLLBACK TRANSACTION TransInsertAndUpdateTimeTaken
END CATCH

Open in new window

0
Comment
Question by:doramail05
  • 7
  • 3
  • 2
  • +3
16 Comments
 
LVL 9

Expert Comment

by:kaminda
ID: 35688596
You can use something like this

 CONVERT(varchar,(@dtEndTime - @dtStartTime ), 108)

If you want miliseconds also
CONVERT(varchar,(@dtEndTime - @dtStartTime ), 104)
0
 
LVL 4

Expert Comment

by:qasim_md
ID: 35688655
Try this and let me know if it worked :::

CONVERT(varchar,(@dtEndTime - @dtStartTime ), 106)
0
 
LVL 1

Author Comment

by:doramail05
ID: 35688831
are we suppose to add something like

dtTimeDiff = CONVERT(varchar,(@dtEndTime - @dtStartTime ), 108)

if possibly could copy the code in the textpad, update it, and repaste into the code segment here,
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
LVL 1

Expert Comment

by:swarg
ID: 35688915
CONVERT(varchar(10),(convert(datetime,@dtEndTime,108)-convert(datetime,@dtStartTime ,108)),108)
0
 
LVL 9

Accepted Solution

by:
kaminda earned 2000 total points
ID: 35689049

CREATE PROCEDURE [dbo].[InsertAndUpdateTimeTaken]


@dtStartTime datetime,
@dtEndTime datetime,
@strAsgnmt varchar(max)

AS
BEGIN TRY

BEGIN TRANSACTION TransInsertAndUpdateTimeTaken
DECLARE @id int; 
Insert into dbo.DailyITSupportAssignment(timestart, timeend, assignmentname) VALUES (@dtStartTime, @dtEndTime, @strAsgnmt) 
set @id = SCOPE_IDENTITY(); 

UPDATE dbo.DailyITSupportAssignment SET timetaken = CONVERT(varchar,(@dtEndTime - @dtStartTime ), 108) 
WHERE studentid = @id;

COMMIT TRANSACTION TransInsertAndUpdateTimeTaken 

END TRY

BEGIN CATCH
         if @@TRANCOUNT > 0
ROLLBACK TRANSACTION TransInsertAndUpdateTimeTaken
END CATCH

Open in new window

0
 
LVL 1

Author Comment

by:doramail05
ID: 35689516
notice that the timetaken is NULL

after inserting the sql statement with dtstarttime and dtendtime,,
0
 
LVL 2

Expert Comment

by:cojdev
ID: 35689578
why not use the DATEDIFF ( datepart , startdate , enddate ) function. Why not calculate the time difference and set the timetaken during the insert.

UPDATE
      dbo.DailyITSupportAssignment
SET
       timetaken = DATEDIFF(<DATEPART> @dtStartTime,  @dtEndTime )
WHERE
     studentid = @id;


DATEPART can be hour, minute, second, millisecond

Here is a complete list http://msdn.microsoft.com/en-us/library/ms189794.aspx.

0
 
LVL 1

Author Comment

by:doramail05
ID: 35689694
k,,

the compiler didnt seems like it,
stated that the DATEDIFF requires 3 arguments,
timetaken = DATEDIFF(<DATEPART> @dtStartTime,  @dtEndTime )
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35689816
you have to specify datepart

timetaken = DATEDIFF(DAY, @dtStartTime,  @dtEndTime )

or

timetaken = DATEDIFF(MINUTES, @dtStartTime,  @dtEndTime )

or

timetaken = DATEDIFF(HOUR, @dtStartTime,  @dtEndTime )

etc
0
 
LVL 1

Author Comment

by:doramail05
ID: 35689827

got NULL,
timetaken = DATEDIFF(HOUR, @dtStartTime,  @dtEndTime )

in this case, it will also compare the Hour and Minute, coz both of it changes,
cant really stated HOUR different, am i right?
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35689861

if either @dtStartTime or  @dtEndTime  is null, the result will also be null

I suppose you can use MINUTE if you want a smaller time interval
timetaken = DATEDIFF(MINUTE, @dtStartTime,  @dtEndTime )
0
 
LVL 1

Author Comment

by:doramail05
ID: 35690054
no, i didnt mean it will compare only like , minute

most often it will compare hour and minute,
so i guess should be hard to put in that only minute or only hour


0
 
LVL 1

Expert Comment

by:swarg
ID: 35695675
Have you tried this

timetaken=CONVERT(varchar(10),(convert(datetime,@dtEndTime,108)-convert(datetime,@dtStartTime ,108)),108)

this will give you time difference.
0
 
LVL 1

Author Comment

by:doramail05
ID: 35695759
ah,,
getting NULL still =,= ~~




UPDATE 
      dbo.DailyITSupportAssignment 
SET 
       timetaken = CONVERT(varchar(10),(convert(datetime,@dtEndTime,108)-convert(datetime,@dtStartTime ,108)),108)
WHERE 
     studentid = @id;

Open in new window

0
 
LVL 1

Expert Comment

by:swarg
ID: 35696769
please provide me the starttime and endtime you are giving.
0
 
LVL 1

Author Comment

by:doramail05
ID: 35697245
they are dtstarttime = 8:30 and dtendtime = 11:30          <--

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question