N K N K - 5 months ago 10
SQL Question

Not able to execute the stored procedure

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

cjk cjk
Answer

You are passing in a date as a string (with implicit time being 00:00) which you are casting to be a date, still with time being 00:00, and trying to match dates with times. There won't be any results as the time doesn't match.

You have to either:

  1. Cast the datetime to a date to match an exact date (not good, requires recalculating every date in the column)
  2. Change the search to look between date + '00:00' to date + '23:59' (or if you are happy, you could just add a day)

Update for your where clause to take the easy option 2:

where a.CUser_id = b.mkey
   and a.U_datetime BETWEEN CONVERT(varchar(10), @As_ONDATE, 103) 
                    AND DATEADD(day, 1, CONVERT(varchar(10), @As_ONDATE, 103))