Post reply

Syntax error converting datetime from character string.

  • May 4, 2007 at 10:38 am

    #116585

    I have a stored proc where in i have the following line of code

    SET @dtStopTime = cast(CONVERT(varchar(10),GETDATE(),110) + ' 23:59:59.000' as datetime)

    This proc works fine when runs from Query analyzer and also works fine when runs as part of job on a scheduled time.

    But This code is failing and returning the error (subject line) when This procedure was invoked when SQL Server is restarted. ( I have checked the checkbox "execute whenever SQL Server starts"on the procedure, its available as the proc is from master db).

    My procedure is lying in Master DB and i want this proc to be executed whenever SQL Server Started. also this proc is scheduled to run every day midnight. So, This procedure should run every day midnight as per schedule(This is working fine) and also this proc should run when SQL Server Starts ( this is failing).

    Any help would be highly appreciated.

  • May 4, 2007 at 10:52 am

    #704423

    What is the goal of this SP fout of curiosity?

  • May 4, 2007 at 10:57 am

    #704425

    This SP will invoke at midnight to start capturing traces and willl stop at 11:59:59:00 PM on the same day to generate trace file of all the events captured that day.

  • May 4, 2007 at 12:50 pm

    #704452

    SELECT @dtStoptime = CONVERT(DATETIME,CONVERT(VARCHAR(11),GETDATE(),120)+ '23:59:59.999',120)

    LinkedIn Profile

  • May 4, 2007 at 1:13 pm

    #704457

    Or try this so you are not convert between varchar and datetime at all.

    SET @dtStopTime = dateadd(s,-1,dateadd(d,datediff(d,0,GETDATE()) + 1,0))

     

    Not sure why it would be an issue thou. Might dig later.

  • May 4, 2007 at 1:34 pm

    #704462

    Thanks david..your solution works..thanks a lot...

  • May 4, 2007 at 5:25 pm

    #704486

    David's solution is almost correct, but not quite... the resolution for DATETIME datatypes is 3 milliseconds... David's solution rounds up to midnight... you need to do this instead...

    SELECT @dtStoptime = CONVERT(DATETIME,CONVERT(VARCHAR(11),GETDATE(),120)+ '23:59:59.997',120)

    Really, it would be better if your code did round up to midnight and then use < instead of <= in any of your enddate comparisons.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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