Not generating run time

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

  • 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/

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

  • 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.

  • 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.

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

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

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