Works in QA but not as a Job

Posted on 2006-05-25
Last Modified: 2012-06-22
Hello I have a SPROC that runs an update query. The update query uses a function as part of the WHERE clause. The function takes in a start and end date and also start time and end time and returns working time.

When I run the SPROC in query analyser it runs fine but then when I call the SPROC from a Job to run over night it doesn't work giving the error message

"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. [SQLSTATE 22007] (Error 242).  The step failed."

Is there an obvious reason for this??


Question by:treanor99
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    Post the query and the dateformat
    LVL 5

    Expert Comment

    may be from some reason the Job's sessios has unexpected DATEFORMAT. try to ensure the format you need by using "Set DATEFORMAT". for example: do: SET DATEFORMAT mdy and then the select statment.
    LVL 1

    Author Comment

    The SPROC
          Allow_Carriage = 0,
          Allow_Postage = 0,
          Allow_Labour_Prod = 0,
          Allow_Labour_Auto = 0,
          Allow_Labour_Other = 0,
          Allow_Materials = 0,
          Allow_Clicks = 0,
          Allow_Space = 0,
          Allow_SupplierInvoices = 0
          FROM tblProject
          WHERE project_id > 38000
          AND Dead_Date IS NOT NULL
          AND dbo.f_get_working_time(Dead_Date, GETDATE(), '08:30:00', '17:30:00') > 540
          AND dbo.f_get_working_time(Dead_Date, GETDATE(), '08:30:00', '17:30:00') < 1080
    The Function
          (@datetime_from datetime, @datetime_to datetime, @Start_Time varchar(8), @Finish_Time varchar(8))

          -- @datetime_from  '20/11/2005 09:00:00' , @datetime_to  '20/11/2005 11:00:00',  @Start_Time  '08:00', @Finish_Time '17:00'

    RETURNS int


    IF @datetime_from IS NULL OR @datetime_to IS NULL
                Return NULL

    -- Returns the actual working time available between two datetimes in minutes.  enter the two dates and the start and finish times of the working day e.g. see above

          DECLARE @date_this datetime, @date_from datetime, @date_to datetime, @time_from datetime, @time_to datetime,  @days int, @TimeMins int

          -- initialise count

          SELECT @date_from = convert(datetime,(CONVERT(varchar(10),@datetime_from,103))), @date_to = convert(datetime,(CONVERT(varchar(10),@datetime_to,103)))

          SELECT @date_this = @date_from, @days = 0, @TimeMins = 0

          -- loop through dates
          WHILE @date_this <= @datetime_to BEGIN

                IF DATEPART(dw, @date_this) < 6
                AND NOT EXISTS (SELECT * FROM holiday WHERE date_holiday = @date_this)
                -- this is a working day (not a saturday, sunday or bank holiday)

                      IF @days = 0
                            SELECT @time_from = @datetime_from
                            SELECT @time_to = convert(datetime, @date_this + ' ' + @Finish_Time)
                            IF @datetime_to < @time_to
                                  SELECT @time_to = @datetime_to
                      IF @days > 0
                            IF @date_this <> @date_to
                                  SELECT @time_from = convert(datetime, @date_this + ' ' + @Start_Time)
                                  SELECT @time_to = convert(datetime, @date_this + ' ' + @Finish_Time)
                            IF @date_this = @date_to
                                    SELECT @time_from = convert(datetime, @date_this + ' ' + @Start_Time)
                                  SELECT @time_to = @datetime_to

                    SELECT @TimeMins = @TimeMins + ISNULL(DATEDIFF(mi,@time_from,@time_to),0)

                -- next day
                SELECT @days = @days + 1
                SELECT @date_this = DATEADD(dd, 1, @date_this)

          END -- loop
          -- return time
          Return @TimeMins
    LVL 75

    Accepted Solution

    >    SELECT @date_from = convert(datetime,(CONVERT(varchar(10),@datetime_from,103))),
    > @date_to = convert(datetime,(CONVERT(varchar(10),@datetime_to,103)))

    the above line has to be

    SELECT @date_from = convert(datetime, (CONVERT(varchar(10),@datetime_from,103)) ,103),
     @date_to = convert(datetime,(CONVERT(varchar(10),@datetime_to,103)),103)
    LVL 1

    Author Comment

    Cheers aneeshattingal, works like a charm!


    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now