Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Not generating run time Expand / Collapse
Author
Message
Posted Monday, November 5, 2012 9:04 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, December 11, 2014 11:23 AM
Points: 337, Visits: 598
I have the SQL, listed below, what I would like to do is to get the fractions down to two places (0.25), but looking at the convert function it looks like the min is 6. Any ideas?

set @message = 'time ' + cast((cast(datediff(ss,@startdate,getdate()) as int)/60) as varchar(12))

Post #1381141
Posted Monday, November 5, 2012 9:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:05 PM
Points: 13,327, Visits: 12,820
dwilliscp (11/5/2012)
I have the SQL, listed below, what I would like to do is to get the fractions down to two places (0.25), but looking at the convert function it looks like the min is 6. Any ideas?

set @message = 'time ' + cast((cast(datediff(ss,@startdate,getdate()) as int)/60) as varchar(12))



Like this?
select 'time ' + cast(cast(cast(datediff(ss,@startDate - 1,getdate()) as int)/60. as numeric(9,2)) as varchar(15))



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1381148
Posted Monday, November 5, 2012 9:21 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 11, 2014 1:54 PM
Points: 25, Visits: 154
Hi,

what is your @startdate datatype.

Also can you give an example on what the parameters actual value are and what you want it to look like.

Thanks
Satyen


sorry didnt see the reply.
good work!!!
Post #1381151
Posted Monday, November 5, 2012 10:03 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, December 11, 2014 11:23 AM
Points: 337, Visits: 598
It is datetime..

declare @startdate datetime

and I am using getdate() to load into @startdate, at the top of the program.

So I would expect to see ... lets say the program took 28min , I would expect to see the following:

time 0.25 time 0.40 time 1.50 time 6.33 time 9.75 time 13.12 time 25.05 time 26.80 time 28.0

Note my code has a label before time, so I know what statement was last executed.
Post #1381181
Posted Monday, November 5, 2012 10:08 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, December 11, 2014 11:23 AM
Points: 337, Visits: 598
Oh, and I am also using @@rowcount to show the number of records. After this things will get a bit interesting.. since I will have to capture things that would cause the statement to take a lot longer to run. The program in question runs fine most of the month, but about three days a month it spikes and runs about four times as long. I am writing the @message, and the job name, into a table so I can capture the normal and spikes.. and I hope to find out why it spikes.
Post #1381187
Posted Monday, November 5, 2012 10:11 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, December 11, 2014 11:23 AM
Points: 337, Visits: 598
Thanks Sean, I use the numeric data type so seldom I often forget about it. (I tend to use float or one of the int)

Post #1381188
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse