MS SQL 2000 Function ' DATEADD ' Problem

Hello Experts,
I'm Using MS SQL2000

What the different between following spTestRPT1.sql and spTestRPT2.sql ?

spTestRPT1.sql  Doesnt like  DATEADD("hh",23,@p_DateTo_IN)
spTestRPT2.sql Doesnt mind either DATEADD("hh",23,@p_DateTo_IN) or DATEADD(hh,23,@p_DateTo_IN)
Error Message for spTestRPT1.sql
Server: Msg 1023, Level 15, State 1, Procedure spTestRPT1, Line 30
Invalid parameter 1 specified for dateadd.

Whats the reason for this? Anything to do with header? Well I try switching header information as well.
Its not a big issue since I figure it out one work without quotation mark. But whats the reason for this?
Udana


spTestRPT1.sql
 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spTestRPT1]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spTestRPT1]
GO
 
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
 
CREATE Procedure spTestRPT1
(
	@p_LoginContactId_IN integer,
	@p_DateFrom_IN datetime,
	@p_DateTo_IN datetime,
	@p_StateID_IN integer,
	@p_NetworkID_IN integer
)
AS 
 
SET @p_DateTo_IN = DATEADD(hh,23,@p_DateTo_IN) 
SET @p_DateTo_IN = DATEADD(mi,59,@p_DateTo_IN)
SET @p_DateTo_IN = DATEADD(ss,59,@p_DateTo_IN)
 
 
 
 
---> Continues
 
spTestRPT2.sql
 
 
USE TEST
Go
-----
IF EXISTS (SELECT '*' 
           FROM sysobjects 
           WHERE ID = object_id('[dbo].[spTestRPT2]') 
          and OBJECTPROPERTY(ID, 'IsProcedure') = 1)
DROP PROCEDURE [dbo].[spTestRPT2]
GO
 
CREATE PROCEDURE spTestRPT2
(
	@p_LoginContactId_IN 	integer,
	@p_StatusID_IN  	as smallint,		---4
	@p_DateFrom_IN		as datetime,		---8
	@p_DateTo_IN 		as datetime		---9
 
)
 AS
 
SET @p_DateTo_IN = DATEADD("hh",23,@p_DateTo_IN)
SET @p_DateTo_IN = DATEADD("mi",59,@p_DateTo_IN)
SET @p_DateTo_IN = DATEADD("ss",59,@p_DateTo_IN)
 
---> continues

Open in new window

udanabananaAsked:
Who is Participating?
 
imitchieConnect With a Mentor Commented:
Looking at the header information, I hazard a guess that spTestRPT1 has a matching

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

at the bottom.  (part of the ..continues.. that you have omitted)

When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers.

The better way to use DATEADD is NOT to quote the first parameter.  What SQL Server 2005 _allows_ you to do is quote it, provided SET QUOTED_IDENTIFIER is ON.
0
 
Alpha AuCommented:
According to the example used in MSDN
http://msdn2.microsoft.com/en-us/library/aa258267(SQL.80).aspx

the dateparts Is the parameter that specifies on which part of the date to return a new value.
The table lists the dateparts and abbreviations recognized by Microsoft® SQL Server™.

that's mean it does not need double quote
0
 
PaultheBrokerCommented:
I think that SET QUOTED_IDENTIFIER ON (the default behaviour) will produce the beahvoiur you are seeing in RPT2 - I can't answer why the flag was reset between the two create statements, if you've provided a full listing....as you set it OFF for the RPT1
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
PaultheBrokerCommented:
... ok, I now see you tried changing the header round, and this had no effect... I give up...what's the answer??? :)
0
 
udanabananaAuthor Commented:
imitchie
you are correct! Thanks mate.
Udana
IF @@rowcount = 1
BEGIN	
	SELECT @@identity
    	RETURN 0
END
ELSE
    	RETURN 1
 
 
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
 
GRANT  EXECUTE  ON [dbo].[spTestRPT1]  TO [TEST_USER]
GO

Open in new window

0
 
udanabananaAuthor Commented:
Thanks Guys for the Help.
0
 
udanabananaAuthor Commented:
450 points  to imitichie for the explaination.
50 points to PaultheBroker for valuable link.
0
All Courses

From novice to tech pro — start learning today.