What is the equivalent of ORACLE “MONTH_between “ in SQL ?

• VGish

  SSChasing Mays

  Points: 643

  More actions

  July 16, 2012 at 12:51 pm

  #259558

  Hi,

  What is the equivalent of ORACLE “MONTH_between “ in SQL ? I want in months….

  I tried the Datediff, It is not the same..

  See the example below…

  select FLOOR(MONTHS_BETWEEN(sysdate,'07/20/2011'))

  from dual; -- Gives 11

  select DATEDIFF (mm,'07/20/2011' ,getdate()) -- Gives 12

  Thanks,

  VG

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 16, 2012 at 1:07 pm

  #1513263

  VGish (7/16/2012)

  ---

  Hi,

  What is the equivalent of ORACLE “MONTH_between “ in SQL ? I want in months….

  I tried the Datediff, It is not the same..

  See the example below…

  select FLOOR(MONTHS_BETWEEN(sysdate,'07/20/2011'))

  from dual; -- Gives 11

  select DATEDIFF (mm,'07/20/2011' ,getdate()) -- Gives 12

  Thanks,

  VG

  That is because DATEDIFF is counting the number of MONTH boundaries between the two dates. It does not take into account the day of the month. Not having used MONTHS_BETWEEN, I can only guess that since you are taking the FLOOR of the this calculation that it takes into account the day of the month as well.

  Try this if what you want needs to include the day of the month in the calculation:

  select DATEDIFF(mm,'07/20/2011' ,getdate()) - CASE WHEN GETDATE() < DATEADD(mm, DATEDIFF(mm,'07/20/2011',getdate()), '07/20/2011') THEN 1 ELSE 0 END

• GSquared

  SSC Guru

  Points: 260824

  More actions

  July 16, 2012 at 1:14 pm

  #1513268

  There isn't a direct equivalent.

  If you want the number of months from one date to another, DateDiff will give the number of month-transitions in that span, but not precisely the "number of months".

  Of course, the Oracle function has its own issues. It assumes all months are 31 days, per its documentation. Not an issue here, since you want just the months, but definitely an oddity in the code.

  Use a Case statement in your select. That's probably easiest. DateDiff-1 if the second "day" is less than the first one, or DateDiff if it's >=.

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

• ScottPletcher

  SSC Guru

  Points: 101200

  More actions

  July 16, 2012 at 3:32 pm

  #1513346

  I'm not familiar with MONTHS_BETWEEN, but IF it does always use 31 days as a "month" in that function, then you can get the same result from SQL Server using:

  SELECT DATEDIFF(DAY, '20110720', GETDATE()) / 31

  which does in fact yield "11".

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".