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