Need help on varchar operation

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Need help on varchar operation

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2009-11-14 : 01:25:26

my statement as follow,
declare @yyyymm as varchar(6)
set @yyyymm='200910'

i want my resultset as follow,
Prev mmmmyy | Next yyyymm
-------------------------------
200909 | 200911

how my SQL look's like?

-- yyyymm format is 200902, 200905, 200911, 201002, 201011 and so on

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-11-14 : 02:12:33

declare @yyyymm as varchar(6)
set @yyyymm='200910'

select cast(year(dateadd(m,1,cast( @yyyymm+'01' as datetime))) as varchar(4))+
cast(month(dateadd(m,1,cast( @yyyymm+'01' as datetime))) as varchar(4)) as Next_month,
cast(year(dateadd(m,-1,cast( @yyyymm+'01' as datetime))) as varchar(4))+
cast(month(dateadd(m,-1,cast( @yyyymm+'01' as datetime))) as varchar(4)) as Previous_month

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2009-11-14 : 07:09:14

quote:
Originally posted by senthil_nagore

declare @yyyymm as varchar(6)
set @yyyymm='200910'

select cast(year(dateadd(m,1,cast( @yyyymm+'01' as datetime))) as varchar(4))+
cast(month(dateadd(m,1,cast( @yyyymm+'01' as datetime))) as varchar(4)) as Next_month,
cast(year(dateadd(m,-1,cast( @yyyymm+'01' as datetime))) as varchar(4))+
cast(month(dateadd(m,-1,cast( @yyyymm+'01' as datetime))) as varchar(4)) as Previous_month

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

I got
Next_Month | Previous_Month
--------------------------------
200911 | 20099

Actually, my format is yyyymm. It's suppose 20099 shown as 200909

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-14 : 09:12:32

A little modification to Senthil's solution:

declare @yyyymm as varchar(6)
set @yyyymm='200910'


select 
cast(year(dateadd(m,1,cast( @yyyymm+'01' as datetime))) as varchar(4))+
right('00'+cast(month(dateadd(m,1,cast( @yyyymm+'01' as datetime))) as varchar(4)),2) as Next_month,

cast(year(dateadd(m,-1,cast( @yyyymm+'01' as datetime))) as varchar(4))+
right('00'+cast(month(dateadd(m,-1,cast( @yyyymm+'01' as datetime))) as varchar(4)),2) as Previous_month

No, you're never too old to Yak'n'Roll if you're too young to die.

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2009-11-14 : 11:09:49

thanks to all.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-16 : 01:54:32

Power of implicit convertion


declare @yyyymm as varchar(6)
set @yyyymm='200910'

select 
	@yyyymm*1+'1' as next_month,
	@yyyymm*1-'1' as previous_month

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources