Improve company productivity with a Business Account.Sign Up

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

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
0
MickeyMin
Asked:
MickeyMin
  • 4
  • 3
1 Solution
 
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
 
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
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
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
 
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.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

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