-1

I have month names in my database, for eg. tomonth and frommonth. The data stored is like 'JAN', 'FEB' etc.

What do I have to do to get difference between tomonth and frommonth?

Ben
  • 51,770
  • 36
  • 127
  • 149
aniket876
  • 11
  • 1
  • 5
  • 6
    How do you know which year? Does it matter? Have you considered storing dates as dates? How do you want the "difference" rendered? The number of months? – Ben Mar 18 '13 at 09:20
  • PERIOD_DIFF calculates months between two dates. http://stackoverflow.com/questions/288984/the-difference-in-months-between-dates-in-mysql – David Karlsson Mar 18 '13 at 09:24
  • 1
    @DavidKarlsson:PERIOD_DIFF calculates months between period. he is storing as string. – divyabharathi Mar 18 '13 at 09:27
  • I completely agree with @Ben's comment, above. However, if you genuinely want to record solely the month and not a particular month/year or date, why not store the month as an integer (1=Jan, 12=Dec)? – eggyal Mar 18 '13 at 09:28

2 Answers2

0

Try like below query

select (tomonth-frommonth) as monthdiff from
( SELECT case when `frommonth` = 'Jan' then 1 
   when frommonth = 'Feb' then 2  
   when frommonth = 'Mar' then 3 
   when frommonth = 'Apr' then 4  
   when frommonth = 'May' then 5  
   when frommonth = 'Jun' then 6  
   when frommonth = 'Jul' then 7  
   when frommonth = 'Aug' then 8  
   when frommonth = 'Sep' then 9  
   when frommonth = 'Oct' then 10  
   when frommonth = 'Nov' then 11  
   when frommonth = 'Dec' then 12 
   end frommonth, 
( case when tomonth = 'Jan' then 1
   when tomonth = 'Feb' then 2  
   when tomonth = 'Mar' then 3 
   when tomonth = 'Apr' then 4  
   when tomonth = 'May' then 5  
   when tomonth = 'Jun' then 6  
   when tomonth = 'Jul' then 7  
   when tomonth = 'Aug' then 8  
   when tomonth = 'Sep' then 9  
   when tomonth = 'Oct' then 10  
   when tomonth = 'Nov' then 11  
   when tomonth = 'Dec' then 12 
   end ) as tomonth from table) a
Dhinakar
  • 4,061
  • 6
  • 36
  • 68
0

Your could use STR_TO_DATE:

SELECT
  MONTH(STR_TO_DATE(tomonth,'%b'))-MONTH(STR_TO_DATE(frommonth,'%b')) as montdiff
FROM
  yourtable
fthiella
  • 48,073
  • 15
  • 90
  • 106