Monthname is not properly showing up when hardcode it

• ramaanujamr

  Old Hand

  Points: 366

  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

• ramaanujamr

  Old Hand

  Points: 366

  More actions

  October 28, 2008 at 9:39 am

  #890733

  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: 323482

  More actions

  October 28, 2008 at 9:41 am

  #890739

  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!