Help Convert to a Date and Back

Hi All,
I have an Stored Procedure.  I want to get the current month end from a string and return the value as a string '20080429'

So I pass in string '20080429'
and it returns a string '20080430'
This is want I've written but it doesn't work....

CREATE PROCEDURE Caoimhe @value_dt varchar(8)
as
declare @current_monthend varchar(8)
declare @monthend varchar(8)
select @monthend = dateadd(day, -1,dateadd(month, 1,convert(datetime,left(@value_dt,6) + '01',112)))
select @current_monthend = convert(varchar(8),@monthend,112)
return @current_monthend
MickeyMinAsked:
Who is Participating?
 
Aneesh RetnakaranDatabase AdministratorCommented:
CREATE PROCEDURE Caoimhe @value_dt varchar(8),@monthend varchar(8) out
as
select @monthend = CONVERT( varchar(8), dateadd(day, -1,dateadd(month, 1,convert(datetime,left(@value_dt,6) + '01',112))), 112)
GO

DECLARE @Monthend varchar(8)
EXEC Caoimhe '20070709', @Monthend OUT
SELECT @Monthend
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
compute the value of @MonthEnd like this


select @monthend = CONVERT( varchar(8), dateadd(day, -1,dateadd(month, 1,convert(datetime,left(@value_dt,6) + '01',112))), 112)
0
 
MickeyMinAuthor Commented:
I get this..

 22018(257)[DataDirect][ODBC Sybase Wire Protocol driver][SQL Server]Implicit conversion from datatype 'VARCHAR' to 'INT' is not allowed.  Use the CONVERT function to run this query.
 (0.17 secs)

I've changed the SP to this..

CREATE PROCEDURE Caoimhe @value_dt varchar(8)
as



declare @current_monthend varchar(8)
declare @monthend varchar(8)
select @monthend = CONVERT( varchar(8), dateadd(day, -1,dateadd(month, 1,convert(datetime,left(@value_dt,6) + '01',112))), 112)



return @current_monthend

thanks
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Aneesh RetnakaranDatabase AdministratorCommented:
CREATE PROCEDURE Caoimhe @value_dt varchar(8)
as
declare @current_monthend varchar(8)
declare @monthend varchar(8)
select @monthend = CONVERT( varchar(8), dateadd(day, -1,dateadd(month, 1,convert(datetime,left(@value_dt,6) + '01',112))), 112)
return @monthend
0
 
MickeyMinAuthor Commented:
I'm sorry but I get the same error

Thanks
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
is it sybase ? or sql server ?
0
 
MickeyMinAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.