I have table in which my date column value is saved with time also
like this 2016-06-10 14:56:11.000
Now while executing my SP, I pass one parameter as date like this exec UserReportData '06-10-2016' but it is not showing any records. As it has 4 records in the table.
why?
UPDATE
ALTER PROCEDURE [dbo].[UserReportData]
@As_ONDATE Datetime
AS
BEGIN
DECLARE @REPORTDATE datetime
DECLARE @OPENING INT
Select * INTO #temptable
FROM
(select distinct a.CUser_id, b.User_Id,a.U_datetime as REPORTDATE, b.first_name + ' ' + b.last_name AS USERNAME,
0 OPENING, 0 TOTAL_DOCUMENT, 0 INWARD, 0 FIRST_LEVEL_PROCESSING, 0 DATA_ENTRY
from inward_doc_tracking_trl a, user_mst b
where a.CUser_id = b.mkey
and a.U_datetime = CONVERT(varchar(10), @As_ONDATE, 103)
) as x
DECLARE Cur_1 CURSOR
FOR SELECT CUser_id, User_Id FROM #temptable
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
SELECT @REPORTDATE FROM inward_doc_tracking_trl
where U_datetime = CONVERT(varchar(10), @As_ONDATE, 103)
UPDATE #temptable
SET REPORTDATE = @REPORTDATE
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 #temptable
DROP TABLE #temptable
END
Aucun commentaire:
Enregistrer un commentaire