Archives of the TeradataForum

Message Posted: Tue, 31 Oct 2006 @ 19:03:59 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

You might need a more precise definition of "exact months between two given dates."

Several people have suggested something like

     Select ( date2 - date1) month

But that might not be what you want:

     select (cast('2006-11-30' as date) - cast('2006-10-01' as date)) month = 1
     select (cast('2006-11-30' as date) - cast('2006-10-30' as date)) month = 1
     select (cast('2006-11-30' as date) - cast('2006-10-31' as date)) month = 0
     select (cast('2006-12-01' as date) - cast('2006-10-01' as date)) month = 2
     select (cast('2006-12-01' as date) - cast('2006-10-30' as date)) month = 1
     select (cast('2006-12-30' as date) - cast('2006-10-31' as date)) month = 1
     select (cast('2006-12-31' as date) - cast('2006-10-31' as date)) month = 1
     select (cast('2006-12-31' as date) - cast('2006-10-30' as date)) month = 2

Look at the first three rows - in each of them, every day in November is in the date range being looked at, but the last one doesn't count a full month as having passed. This isn't necessarily wrong, it just might not be what you need.

Your best bet is to look at your requirements and write SQL specifically for them. For example, suppose you want to count a month if the months are different even if only one day has passed. In that case, you could do something like

     Select ((date2/10000 - date1/10000) * 12)
                + (extract (month from date2) - extract(month from date1))

(By the way, this will give you an integer result that you can do additional math with. The (date2 - date1) month calculation gives you an interval datatype, so you might need to cast it somewhere down the line.....)

Dave

	<Prev	Next>		<<First	<Prev	Next>	Last>>