jeudi 19 mai 2016

Adding datetime as a parameter is giving Error converting data type varchar to datetime (Error) in stored procedure

I have a stored procedure which accepts one parameter as @ReportDate.

but when I execute it with parameter it gives me error as

Error converting data type varchar to datetime.

Here is the SP.

ALTER PROCEDURE [dbo].[GET_EMP_REPORT] 
@ReportDate Datetime
AS 
BEGIN
DECLARE @Count INT 
DECLARE @Count_closed INT 
DECLARE @Count_pending INT 
DECLARE @Count_wip INT 
DECLARE @Count_transferred INT 
DECLARE @Count_prevpending INT
    SELECT * 
       INTO #temp 
   FROM ( 
    select distinct a.CUser_id,a.CUser_id User_Id, b.first_name + ' ' + b.last_name NAME, 
    0 RECEIVED, 0 CLOSED, 
    0 PENDING, 0 WIP, 0 TRANSFERRED, 0 PREV_PENDING 
    from inward_doc_tracking_trl a, user_mst b 
    where a.CUser_id = b.mkey 
   ) AS x 
    DECLARE Cur_1 CURSOR 
     FOR SELECT CUser_id, User_Id FROM #temp
    OPEN Cur_1 
    DECLARE @CUser_id INT 
      DECLARE @User_Id INT 
  FETCH NEXT FROM Cur_1 
    INTO @CUser_id, @User_Id 
   WHILE (@@FETCH_STATUS = 0) 
 BEGIN 
     /***** received *******/ 
    SELECT @Count = COUNT(*) FROM inward_doc_tracking_trl 
     WHERE CUser_id = @CUser_id 
   AND NStatus_flag = 4 
   AND CStatus_flag = 1 
   AND U_datetime BETWEEN @ReportDate AND GETDATE()
   /***** closed *******/ 
  SELECT @Count_closed = COUNT(*) FROM inward_doc_tracking_trl 
  WHERE CUser_id = @CUser_id 
    AND NStatus_flag = 5 
   AND U_datetime BETWEEN @ReportDate AND GETDATE()
   /***** pending *******/ 
    SELECT @Count_pending = COUNT(*) FROM inward_doc_tracking_trl trl 
   INNER JOIN inward_doc_tracking_hdr hdr ON hdr.mkey = trl.ref_mkey 
    WHERE trl.N_UserMkey = @CUser_id 
    AND trl.NStatus_flag = 4 
   AND trl.CStatus_flag = 1 
     AND hdr.Status_flag = 4 
    AND trl.U_datetime BETWEEN @ReportDate AND GETDATE()
     /***** wip *******/ 
     SELECT @Count_wip = COUNT(*) FROM inward_doc_tracking_trl trl 
  INNER JOIN inward_doc_tracking_hdr hdr ON hdr.mkey = trl.ref_mkey 
  INNER JOIN (select max(mkey) mkey,ref_mkey from inward_doc_tracking_trl          where NStatus_flag = 2 group by ref_mkey ) trl2 
     ON trl2.mkey = trl.mkey and trl2.ref_mkey = trl.ref_mkey 
     WHERE trl.N_UserMkey = @CUser_id 
   AND trl.NStatus_flag = 2 
    AND hdr.Status_flag = 2 
  AND trl.U_datetime BETWEEN @ReportDate AND GETDATE() 
      /***** transferred *******/ 
      SELECT @Count_transferred = COUNT(*) FROM inward_doc_tracking_trl 
      WHERE CUser_id = @CUser_id 
   AND NStatus_flag = 4 
    AND CSTATUS_flag <> 1 
      AND U_datetime BETWEEN @ReportDate AND GETDATE()
   /******** Previous pending **********/ 
    SELECT @Count_prevpending = COUNT(*) FROM inward_doc_tracking_trl trl 
   INNER JOIN inward_doc_tracking_hdr hdr ON hdr.mkey = trl.ref_mkey 
     INNER JOIN (select max(mkey) mkey,ref_mkey from inward_doc_tracking_trl        where NStatus_flag = 2 group by ref_mkey ) trl2 
    ON trl2.mkey = trl.mkey and trl2.ref_mkey = trl.ref_mkey 
     WHERE trl.N_UserMkey = @CUser_id 
    AND trl.NStatus_flag = 2 
   AND hdr.Status_flag = 2 
   AND trl.U_datetime < @ReportDate
    UPDATE #temp 
   SET RECEIVED = @Count, 
      CLOSED = @Count_closed, 
    PENDING = @Count_pending, 
   WIP = @Count_wip, 
       TRANSFERRED = @Count_transferred, 
    PREV_PENDING = @Count_prevpending 
     WHERE CUser_id = @CUser_id 
   AND User_Id = @User_Id
        FETCH NEXT FROM Cur_1 INTO @CUser_id, @User_Id 
  END 
     CLOSE Cur_1 
  DEALLOCATE Cur_1 
      SELECT * FROM #temp 
  END

I am executing like this EXEC GET_EMP_REPORT '16/05/2016'

The current date format entered is DD/MM/YYYY which gives me the error. Executing it as MM/DD/YYYY works but I would prefer executing it as DD/MM/YYYY.

but getting error

I am using SQL-server-2005

Aucun commentaire:

Enregistrer un commentaire