Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Date to String Conversion

Posted on 2008-06-20
2
Medium Priority
?
2,213 Views
Last Modified: 2010-04-21
Does anyone know why this won't work in Sybase but works in MS SQl

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 = CONVERT( varchar(8), dateadd(day, -1,dateadd(month, 1,convert(datetime,left(@value_dt,6) + '01',112))), 112)

return @monthend
0
Comment
Question by:MickeyMin
2 Comments
 
LVL 19

Accepted Solution

by:
grant300 earned 2000 total points
ID: 21832912
Assuming ASE.  Please confirm which Sybase product and version you are using.

It works fine for me...

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

Returns "20080430"

Oh, I see what the issue is.  You are trying to use the RETURN statement to get the output value.  That is a no-no for stored procedures.  You really need to use a stored function.

ASE v15 supports T-SQL function and the CREATE FUNCTION syntax.  You are trying to use the CREATE PROCEDURE syntax.  With a procedure, any return value other than 0 is seen as an error.

If you are using a version of Sybase prior to 15, you do not have the function syntax available to you.  You have one or two choices: you can call the procedure with the @monthend as and OUTPUT argument or, if you have it licensed, you can create a Java SQL function that will do what you want as well.

Regards,
Bill
0
 

Author Closing Comment

by:MickeyMin
ID: 31469129
Thank you!
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
Mailbox Corruption is a nightmare every Exchange DBA wishes he never has. Recovering from it can be super-hectic if not entirely futile. And though techniques like the New-MailboxRepairRequest cmdlet have been designed to help with fixing minor corr…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses

916 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