Hello All,My script finally works! But I have a minor problem this time, I hope. When I hard code a date value to @DateDetail it works properly in that it deletes duplicate records based on the date value. But when I use this statement: “SET @DateDetail = dateadd(day,-1,GETDATE())” the script works but does not delete the duplicate records based on the date I put in has the parameter.Below is the stored procedure and the results of my runs using the hard coded date value and the derive date value.CODE:CREATE PROCEDURE [dbo].[usp_TEST_SCRIPT] (@DateDetail DATETIME)AS--SET @DateDetail = dateadd(day,-1,GETDATE())SET @DateDetail = '10/13/2007'DECLARE @DateDetailPlusOne datetimeSET @DateDetailPlusOne=dateadd(d,1,@DateDetail)DECLARE @sqlstring Nvarchar(4000)--** DELETE POSSIBLE DUPLICATES **--PRINT 'DELETING DUPLICATE RECORDS'DELETE FROM TEST_DATAWHERE DATE_DETAIL = @DateDetail--** QUERY AND LOAD DATA **--SET @sqlstring='INSERT INTO TEST_DATA SELECT (''' + CONVERT(varchar(11),@DateDetail,101) + '''),CALLS.* FROM OPENQUERY(DATASYS,''SELECT * FROM TEST_DATA WHERE CALLTIME >= TO_DATE('''''+ CONVERT(varchar(12),@DateDetail,101) + ''''',''''MM/DD/YYYY'''')AND CALLTIME <= TO_DATE('''''+ CONVERT(varchar(12),@DateDetailplusone,101) + ''''',''''MM/DD/YYYY'''')'')AS CALLS' PRINT 'LOADING TEST_DATA'EXEC sp_executesql @sqlstringGORESULTS:DELETING DUPLICATE RECORDS(530 row(s) affected)LOADING TEST_DATA(530 row(s) affected)---------------------------------------------------------------------Using a derive date value “SET @DateDetail = dateadd(day,-1,GETDATE())”DELETING DUPLICATE RECORDS(0 row(s) affected)LOADING TEST_DATA(530 row(s) affected)
Hard coded date value vs. derived date value
Posted - 2007-10-14 : 15:45:26
