Post reply

Dateformat different if Stored Procedure is run from a job

  • September 23, 2010 at 2:52 am

    #238590

    Hi there.

    I just spend over an hour wondering why a stored procedure executed as job behaves differently.

    It seems that the date format differs that's why my update query changes more records than I want it to.

    DECLARE @startdate DATETIME

    --means 1st of September 2010

    SET @startdate = '01.09.2010'

    PRINT year(@startdatum)

    PRINT MONTH(@startdatum)

    PRINT DAY(@startdatum)

    UPDATE t

    SET b = 5

    WHERE t.date >= @startdate

    When I call the sp directly, the print statements give the correct result:

    2010

    9

    1

    When executed as job I get the following:

    2010

    1

    9

    So, what is the safest way to assign a date?

    Thanks in Advance,

    Steffen.

  • September 23, 2010 at 3:37 am

    #1226173

    Hi.

    After searching the internet again I stumbled upon this article which contains a nice overview of dateformats and their dependencies from the language setting:

    http://www.karaszi.com/SQLServer/info_datetime.asp

    I changed the assignment to:

    DECLARE @startdate DATETIME

    SET @startdate = '20100901'

    and now everything works like expected.

    Maybe this helps somebody else.

    Steffen.

Viewing 2 posts - 1 through 1 (of 1 total)

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