[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2989
  • Last Modified:

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

0
udanabanana
Asked:
udanabanana
2 Solutions
 
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
 
PaultheBrokerCommented:
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
PaultheBrokerCommented:
... ok, I now see you tried changing the header round, and this had no effect... I give up...what's the answer??? :)
0
 
imitchieCommented:
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
 
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

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now