Solved

MS SQL 2000 Function ' DATEADD ' Problem

Posted on 2007-11-25
8
2,947 Views
Last Modified: 2010-04-21
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
Comment
Question by:udanabanana
8 Comments
 
LVL 7

Expert Comment

by:alphaau
ID: 20347909
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
 
LVL 6

Expert Comment

by:PaultheBroker
ID: 20347971
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
 
LVL 6

Assisted Solution

by:PaultheBroker
PaultheBroker earned 50 total points
ID: 20347976
0
 
LVL 6

Expert Comment

by:PaultheBroker
ID: 20348027
... ok, I now see you tried changing the header round, and this had no effect... I give up...what's the answer??? :)
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 25

Accepted Solution

by:
imitchie earned 450 total points
ID: 20348166
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
 

Author Comment

by:udanabanana
ID: 20348375
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
 

Author Comment

by:udanabanana
ID: 20348383
Thanks Guys for the Help.
0
 

Author Closing Comment

by:udanabanana
ID: 31410908
450 points  to imitichie for the explaination.
50 points to PaultheBroker for valuable link.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
sql server query? 6 28
SQL Server 2012 Row Selection 2 30
Caste datetime 2 24
SQL JOIN + SUBQUERY? 3 14
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now