Monthname is not properly showing up when hardcode it

Question

Post reply

Monthname is not properly showing up when hardcode it

ramaanujamr

Old Hand

Points: 396
More actions
October 28, 2008 at 9:29 am

#200597

i have yearmonth like
20071
20072
20073
20074
20075
20076
20077
20078
20079
200710
200711
200712
i want to have like
2007January
2007February
2007March
2007April
so i tried with
select yearmonth,
CASE
WHEN (substring ( [YearMonth] ,5,1)='1' ) THEN (substring ( [YearMonth] ,1,4) +'January')
WHEN (substring ( [YearMonth] ,5,1)='2' ) THEN (substring ( [YearMonth] ,1,4) +'February')
WHEN (substring ( [YearMonth] ,5,1)='3' ) THEN (substring ( [YearMonth] ,1,4) +'March')
WHEN (substring ( [YearMonth] ,5,1)='4' ) THEN (substring ( [YearMonth] ,1,4) +'April')
WHEN (substring ( [YearMonth] ,5,1)='5' ) THEN (substring ( [YearMonth] ,1,4) +'May')
WHEN (substring ( [YearMonth] ,5,1)='6' ) THEN (substring ( [YearMonth] ,1,4) +'June')
WHEN (substring ( [YearMonth] ,5,1)='7' ) THEN (substring ( [YearMonth] ,1,4) +'July')
WHEN (substring ( [YearMonth] ,5,1)='8' ) THEN (substring ( [YearMonth] ,1,4) +'August')
WHEN (substring ( [YearMonth] ,5,1)='9' ) THEN (substring ( [YearMonth] ,1,4) +'September')
WHEN (substring ( [YearMonth] ,5,2)='10' ) THEN (substring ( [YearMonth] ,1,4) +'October')
WHEN (substring ( [YearMonth] ,5,2)='11' ) THEN (substring ( [YearMonth] ,1,4) +'November')
WHEN (substring ( [YearMonth] ,5,2)='12' ) THEN (substring ( [YearMonth] ,1,4) +'December')
ELSE
NULL
END as yearmonthname from datetime
but the result is
until month 9 is fine it gives january for 10month ,11 and 12month
it display like
2007january
to2007 september
for 200710= it display 2007january
for200711= it displays 2007january
200712 also samelike 2007 january
if i make WHEN (substring ( [YearMonth] ,5,0)='1' ) =january
it display correctly for month 10 ,11, and 12 but january shows as null
could you please provide solution ?
Thanks
Ram

Viewing 3 posts - 1 through 2 (of 2 total)

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

ramaanujamr Old Hand Points: 396 More actions · Answer 1

i have fixed it thanks

i did small mistake

select yearmonth,

CASE

WHEN (substring ( [YearMonth] ,5,5)='1' ) THEN (substring ( [YearMonth] ,1,4) +'January')

WHEN (substring ( [YearMonth] ,5,5)='2' ) THEN (substring ( [YearMonth] ,1,4) +'February')

WHEN (substring ( [YearMonth] ,5,5)='3' ) THEN (substring ( [YearMonth] ,1,4) +'March')

WHEN (substring ( [YearMonth] ,5,5)='4' ) THEN (substring ( [YearMonth] ,1,4) +'April')

WHEN (substring ( [YearMonth] ,5,5)='5' ) THEN (substring ( [YearMonth] ,1,4) +'May')

WHEN (substring ( [YearMonth] ,5,5)='6' ) THEN (substring ( [YearMonth] ,1,4) +'June')

WHEN (substring ( [YearMonth] ,5,5)='7' ) THEN (substring ( [YearMonth] ,1,4) +'July')

WHEN (substring ( [YearMonth] ,5,5)='8' ) THEN (substring ( [YearMonth] ,1,4) +'August')

WHEN (substring ( [YearMonth] ,5,5)='9' ) THEN (substring ( [YearMonth] ,1,4) +'September')

WHEN (substring ( [YearMonth] ,5,6)='10' ) THEN (substring ( [YearMonth] ,1,4) +'October')

WHEN (substring ( [YearMonth] ,5,6)='11' ) THEN (substring ( [YearMonth] ,1,4) +'November')

WHEN (substring ( [YearMonth] ,5,6)='12' ) THEN (substring ( [YearMonth] ,1,4) +'December')

ELSE

NULL

END as yearmonthname from datetime

thanks

Ram

Lowell SSC Guru Points: 323518 More actions · Answer 2

you have to check the 2 digit months first, because months 10,11, and 12 all match the january single digit criteria!!

[font="Courier New"]

SELECT yearmonth,

CASE

WHEN (SUBSTRING ( [YearMonth] ,5,2)='10' ) THEN (SUBSTRING ( [YearMonth] ,1,4) +'October')

WHEN (SUBSTRING ( [YearMonth] ,5,2)='11' ) THEN (SUBSTRING ( [YearMonth] ,1,4) +'November')

WHEN (SUBSTRING ( [YearMonth] ,5,2)='12' ) THEN (SUBSTRING ( [YearMonth] ,1,4) +'December')

--NOW you can check the Jan column!

WHEN (SUBSTRING ( [YearMonth] ,5,1)='1' ) THEN (SUBSTRING ( [YearMonth] ,1,4) +'January')

WHEN (SUBSTRING ( [YearMonth] ,5,1)='2' ) THEN (SUBSTRING ( [YearMonth] ,1,4) +'February')

WHEN (SUBSTRING ( [YearMonth] ,5,1)='3' ) THEN (SUBSTRING ( [YearMonth] ,1,4) +'March')

WHEN (SUBSTRING ( [YearMonth] ,5,1)='4' ) THEN (SUBSTRING ( [YearMonth] ,1,4) +'April')

WHEN (SUBSTRING ( [YearMonth] ,5,1)='5' ) THEN (SUBSTRING ( [YearMonth] ,1,4) +'May')

WHEN (SUBSTRING ( [YearMonth] ,5,1)='6' ) THEN (SUBSTRING ( [YearMonth] ,1,4) +'June')

WHEN (SUBSTRING ( [YearMonth] ,5,1)='7' ) THEN (SUBSTRING ( [YearMonth] ,1,4) +'July')

WHEN (SUBSTRING ( [YearMonth] ,5,1)='8' ) THEN (SUBSTRING ( [YearMonth] ,1,4) +'August')

WHEN (SUBSTRING ( [YearMonth] ,5,1)='9' ) THEN (SUBSTRING ( [YearMonth] ,1,4) +'September')

ELSE

NULL

END AS yearmonthname FROM DATETIME[/font]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!