Not generating run time

Question

Not generating run time

dwilliscp

SSCrazy Eights

Points: 9270
More actions
November 5, 2012 at 9:04 am

#266579

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))

Viewing 6 posts - 1 through 5 (of 5 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

menon.satyen SSC-Addicted Points: 421 More actions · Answer 2

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!!!

dwilliscp SSCrazy Eights Points: 9270 More actions · Answer 3

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.

dwilliscp SSCrazy Eights Points: 9270 More actions · Answer 4

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.

dwilliscp SSCrazy Eights Points: 9270 More actions · Answer 5

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)