Solved

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

Posted on 2016-09-23

1 solution

109 Views

Last Modified: 2016-09-23

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

Question by:zimmer9

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

1 Comment

LVL 52

Accepted Solution

by:Ryan Chong

Ryan Chong earned 500 total points

ID: 418132882016-09-23

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

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Reset a Lost SA Password in Microsoft SQL Server

Article by: Joseph

This article explains how to reset the password of the sa account on a Microsoft SQL Server. The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.

SSAS: Automating SQL Server Agent service logon as administrator

Article by: Dorababu

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes. We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…