How do I resolve the following error using SQL Server 2008 "Invalid parameter 1 specified for datediff"?

zimmer9
zimmer9 used Ask the Experts™
on
How do I resolve the following error using SQL Server 2008?

dtCreated has a data type of datetime and has a value such as "2009-08-03 00:00:00.000"

Msg 1023, Level 15, State 1, Procedure proc_CSL_UnresolvedRequests, Line 3
Invalid parameter 1 specified for datediff.
Msg 1023, Level 15, State 1, Procedure proc_CSL_UnresolvedRequests, Line 5
Invalid parameter 1 specified for datediff.
Msg 1023, Level 15, State 1, Procedure proc_CSL_UnresolvedRequests, Line 7
Invalid parameter 1 specified for datediff.

USE [CSL]
GO
/****** Object:  StoredProcedure [dbo].[proc_CSL_DistinctEntity]    Script Date: 09/23/2016 17:42:33 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[proc_CSL_UnresolvedRequests]
AS
SELECT revID,dtCreated, 'ReversePositive', createdBy, datediff("d", dtcreated,getdate())
FROM tbl_CSL_Reversepositive where _status='Not Completed'
UNION SELECT stopID, dtCreated,'StopPayment/VoidCheck',createdBy, datediff("d", dtcreated,getdate())
FROM tbl_CSL_Stopvoidpay where _status='Not Completed'
UNION SELECT undeliverID, dtCreated, 'UndeliverableChecks',createdBy, datediff("d", dtcreated,getdate())
FROM tbl_CSL_Undeliverablechecks where _status='Not Completed'
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software Team Lead
Commented:
Msg 1023, Level 15, State 1, Procedure proc_CSL_UnresolvedRequests, Line 3
Invalid parameter 1 specified for datediff.
Msg 1023, Level 15, State 1, Procedure proc_CSL_UnresolvedRequests, Line 5
Invalid parameter 1 specified for datediff.
Msg 1023, Level 15, State 1, Procedure proc_CSL_UnresolvedRequests, Line 7
Invalid parameter 1 specified for datediff.
check the DateDiff syntax here:

DATEDIFF (Transact-SQL)
https://msdn.microsoft.com/en-us/library/ms189794.aspx

hence, you need to replace:
datediff("d", dtcreated,getdate())

Open in new window

to:
datediff(day, dtcreated,getdate())

Open in new window

instead

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial