Need help on varchar operation

Question

Post reply

Need help on varchar operation

Sharul Nizam

Ten Centuries

Points: 1302
More actions
November 13, 2009 at 11:57 pm

#73969

my statement as follow,
declare @yyyymm as varchar(6)
set @yyyymm='200910'
i want my resultset as follow,
Prev yyyymm | Next yyyymm
----------------------------------
200909 | 200911
How my SQL look's like?
-- yyyymm format is 200902, 200905, 200911, 201002, 201011 and so on

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Here you go:

declare @yyyymm as varchar(6)

set @yyyymm='200910'

select

CONVERT(varchar(6), DATEADD(mm, CAST(right(@yyyymm,2) as int) - 2, DATEADD(yy, left(@yyyymm,4) - 1900, 0)), 112),

CONVERT(varchar(6), DATEADD(mm, CAST(right(@yyyymm,2) as int), DATEADD(yy, left(@yyyymm,4) - 1900, 0)), 112)

Sharul Nizam Ten Centuries Points: 1302 More actions · Answer 2

Sharul Nizam

Ten Centuries

Points: 1302

November 14, 2009 at 5:14 am

#1079356

tq very much sir

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

Just another slant on this...

declare @yyyymm as varchar(6)

set @yyyymm='200901'

SELECT CONVERT(CHAR(6),CAST(@yyyymm+'01' AS DATETIME) -1,112) AS [Prev yyyymm],

CONVERT(CHAR(6),CAST(@yyyymm+'01' AS DATETIME)+32,112) AS [Next yyyymm]

My question would be two-fold... 1) why are you formatting dates in SQL instead of the GUI and 2) why are you doing it just one row at a time?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 4

Hi

First, to pay my bill for entering this thread 😀

SELECT

CONVERT(CHAR(6), DATEADD(MONTH, -1, CONVERT(DATETIME, @yyyymm + '01', 112)), 112)

,CONVERT(CHAR(6), DATEADD(MONTH, 1, CONVERT(DATETIME, @yyyymm + '01', 112)), 112)

Second, a question - and the more important part of my post:

Why do you save date values as text?

Greets

Flo

Sharul Nizam Ten Centuries Points: 1302 More actions · Answer 5

Jeff Moden (11/14/2009)
Just another slant on this...
declare @yyyymm as varchar(6)
set @yyyymm='200901'
SELECT CONVERT(CHAR(6),CAST(@yyyymm+'01' AS DATETIME) -1,112) AS [Prev yyyymm],
CONVERT(CHAR(6),CAST(@yyyymm+'01' AS DATETIME)+32,112) AS [Next yyyymm]
My question would be two-fold... 1) why are you formatting dates in SQL instead of the GUI and 2) why are you doing it just one row at a time?

Me partition table followed by tbl_YYYYMM. When user passing the date, my program need to seek which table.

Me partition table into tbl_YYYYMM because of a i dont want to store a lot of row (million row) in a one table. -- me still looking the best pactice for this

Sharul Nizam Ten Centuries Points: 1302 More actions · Answer 6

Florian Reischl (11/14/2009)
Hi
First, to pay my bill for entering this thread 😀
SELECT
CONVERT(CHAR(6), DATEADD(MONTH, -1, CONVERT(DATETIME, @yyyymm + '01', 112)), 112)
,CONVERT(CHAR(6), DATEADD(MONTH, 1, CONVERT(DATETIME, @yyyymm + '01', 112)), 112)
Second, a question - and the more important part of my post:
Why do you save date values as text?
Greets
Flo

me partition table into tbl_yyyyMM