• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 309
  • Last Modified:

Works in QA but not as a Job

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??

Cheers

C
0
treanor99
Asked:
treanor99
  • 2
  • 2
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
Post the query and the dateformat
0
 
ewildeCommented:
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.
0
 
treanor99Author Commented:
The SPROC
*****************************************************
      SELECT
      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

AS BEGIN


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

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

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

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

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

      END -- loop
      
      -- return time
      Return @TimeMins
********************************************************
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
treanor99,
>    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)
0
 
treanor99Author Commented:
Cheers aneeshattingal, works like a charm!

treanor99.
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now