Months between two dates

[Nandini.t]

Hi all,

I have a start date and End date.my date format is as 'YYYY/MM'. How to calculate months between start date and End date.

example:
I have start date = 1994/01 and End date = 2001/03

Thnks in advance fo any assistance.
Nandini

[blom0344]

You mention 'date' format. Isn't this really stored as a string?

[Nandini.t]

No.
[START DATE] and [END DATE] both are in YYYY/MM format from the model itself.

[blom0344]

and the standard _months_between function does not work?   8)

[charon]

 :o

[Nandini.t]

no its not working... its throwing error.

RQP-DEF-0177
An error occurred while performing operation 'sqlOpenResult' status='-28'.

[CognosPaul]

If its stored as YYYY/MM then it's not a date. You'll need to convert it back to a proper date before you can use the months between function. Which database are you using?

[Nandini.t]

oracle.

[blom0344]

cast(substring([item],1,4),integer)*100+cast(substring([item],6,2),integer)

yields 199401 as numerical value which allows a regular subtraction between calculated items..

[absriram]

cast(substring([item],1,4),integer)*100+cast(substring([item],6,2),integer)

yields 199401 as numerical value which allows a regular subtraction between calculated items..

This won't work if the two dates are in different years.  For example 199401 vs 199501 will give 100 as the months between. The correct approach would be to use _make_timestamp.

Expression for Start Date: _make_timestamp (cast(substring([Start Date],1,4),integer), cast(substring([Start Date],6,2),integer), 1)
Expression for End Date: _make_timestamp (cast(substring([End Date],1,4),integer), cast(substring([End Date],6,2),integer), 1)

Expression for Months between: _months_between([End Date],[Start Date])

Sriram.
http://www.cognosonsteroids.com

[CognosPaul]

As it's Oracle, then the function to_date([Item]+'/01','YYYY/MM/DD') should work to convert it to a date.

[blom0344]

silly me..

Should be:

cast(substring([item],1,4),integer)*12+cast(substring([item],6,2),integer)

I think 'correct' approach is a trifle too much. Multiple solutions may exist

[CognosPaul]

Ask 2 SQL experts how to do something and get 5 different answers. You could probably do some stress testing to see which solution is more efficient, but since they both use string operations performance won't be great. It would be better to find out who encoded the values as yyyy/mm and give them a smack.

Incidentally, I like Ties solution. It makes reduces date operations to simple arithmetic.