how do I use dateadd in SQL2005 stored procedure 2

[DebHanleyRI]

I am hoping someone can help me - I need to add 2 days to a date - the data comes in like '20100730'.

Has anyone used dateadd in a stored procedure before?

I have the following in a stored procedure:
-------------------------------------------------
ALTER proc [dbo].[psp_RecondoDate]
@inDate datetime

AS
declare @outdate datetime
SET @outdate = DATEADD(d,2,@inDate)
RETURN @outDate
-------------------------------------------------
with the above I get the error:
Msg 257, Level 16, State 3, Procedure psp_RecondoDate, Line 20
Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.


If I switch to convert:
--------------------------------------------------------
ALTER proc [dbo].[psp_RecondoDate]
@inDate datetime
AS
--declare @inDate2 datetime

--Set @inDate = '07/14/2010'
--print @indate2
declare @outdate datetime
--SET @outdate = DATEADD(d,2,@inDate)
SET @outdate = convert(@inDate, convert(char(8), dateadd(day, 2, @inDate), 108))
RETURN @outDate
-------------------------------------------------
I get the error:
Msg 102, Level 15, State 1, Procedure psp_RecondoDate, Line 19
Incorrect syntax near '@inDate'.



not all who wander are lost....

 

[markros]

The reason is - SQL Server stored procedures can only return integer values. For your case you have two possible solutions:

ALTER proc [dbo].[psp_RecondoDate]
@inDate datetime

AS
declare @outdate datetime
SET @outdate = DATEADD(d,2,@inDate)
select @outDate

------------
Or

ALTER proc [dbo].[psp_RecondoDate]
@inDate datetime,
@outDate datetime OUTPUT

AS
declare @outdate datetime
SET @outdate = DATEADD(d,2,@inDate)


PluralSight Learning Library

 

[bborissov]

Although markros is right,
I'm really curious WHY you need this?

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[markros]

Yes, good point. There is no need for the SP as it's written. Unless it's just for test purposes.

PluralSight Learning Library

 

[DebHanleyRI]

We use an HL7 engine to move messages from our RIS to both our internal and external partners - however - the engine cannot recognize that this is a date and add 2 days. My results for '20100730' came out as '20100732' and invalid dates fail to pass at our partner.

I am closer to resolving this using Markros 1st solution - I had to change it up a bit for the engine - I will post final solution as soon as I get there. Right now there is a problem with the code below I am trying to work through

ALTER proc [dbo].[psp_RecondoDate]
@inDate varchar(8)

AS
declare @outdate datetime
declare @newInDate datetime

SET @newindate = CONVERT( varchar(8), @inDate,8)
SET @outdate = DATEADD(d,2,@newindate)
--select @outDate
RETURN CONVERT( varchar(8), @outDate,8)


not all who wander are lost....

 

[markros]

Did you read my message? In T-SQL Stored procedure can only return INTEGER value.

PluralSight Learning Library

 

[DebHanleyRI]

I did read your email - and I thank you!

I came up with the following - I'm not returning anything yet but I think I am close

ALTER proc [dbo].[psp_RecondoDate]
@inDate nvarchar(100)

AS
DECLARE @outdate nvarchar(100)
DECLARE @newInDate datetime

SET @newindate = CONVERT(datetime, @inDate,8)
SET @outdate = DATEADD(day,2,@newindate)

RETURN convert(nvarchar,CAST (@outDate as datetime),112)


not all who wander are lost....

 

[markros]

I'm sorry, I give up. I repeated the same thing twice and you still are trying to return a character value.

PluralSight Learning Library

 

[bborissov]

ALTER proc [dbo].[psp_RecondoDate]
            @inDate nvarchar(100)
            
AS
DECLARE @outdate nvarchar(100)
DECLARE @newInDate datetime

SET @newindate = CONVERT(datetime, @inDate,8)
SET @outdate = DATEADD(day,2,@newindate)
 
RETURN CAST(convert(varchar(8),CAST (@outDate as datetime),112) As int)

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.