Post reply

Profiler Stop Time at 11:59PM

  • June 3, 2014 at 2:24 am

    #286040

    Hi Team,

    Configured sql profiler with server side trace, automated the process using a job.

    Scheduled to start daily at 12:00AM and stop time given as 23 hours, using below statement.

    SET @StopTime = DATEADD(HOUR,23,GETDATE())

    How to stop the trace at 11:59PM on same day.

  • June 3, 2014 at 2:38 am

    #1718452

    SET @StopTime = DATEADD(MINUTE,59,DATEADD(HOUR,23,GETDATE()))

    Trace will stop 23 hours and 59 minutes after it starts, so if started at midnight it will stop at 23h59

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 3, 2014 at 2:48 am

    #1718454

    Thank you..:-)

  • June 3, 2014 at 6:28 am

    #1718492

    Hi,

    If i start the Job at 12:00 AM, then below statement will stop the job after 23hrs 59mins.

    SET @StopTime = DATEADD(MINUTE,59,DATEADD(HOUR,23,GETDATE()))

    how to stop the trace at 11:59 directly with out saying stop after 23hrs 59mins.

    job may start at any time, but it should be stopped at 11:59PM.

    Can u please suggest...

  • June 3, 2014 at 6:35 am

    #1718500

    All you need to do there is get the date of the current getdate(), with the time portion removed (easy enough), then add that 23 hours and 59 minutes to that. Almost the same query, just with a bit extra to strip the time off of what GETDATE() returns.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 3, 2014 at 6:59 am

    #1718512

    SET @StopTime = DATEADD(MINUTE,-1,CAST(CAST(GETDATE() AS date) AS datetime)+1);

    SpeedySQL

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

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