Robert Meyer Robert Meyer - 1 year ago 50
SQL Question

SQL - Getting an integer relative to a date column

I have a date column called "Expiry_Date". What I've been asked to do is retrieve an integer based on the month relative to the current, for example:

Current Date: 7th May 2016

MyTable
----------
1) 12th May
2) 31st May
3) 2nd June
4) 11 June
5) 19 June
6) 28th July
7) 1st August


The result would be:

1) 1
2) 1
3) 2
4) 2
5) 2
6) 3
7) 4


So, it's getting the month number relative to the current for the given date (or periods). This also has to work across years so "1 January 2017" would be "9" etc.

Many thanks

Answer Source

Perhaps the function MONTHS_BETWEEN solves.

https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions089.htm

Something like:

round(months_between(expiry_date, date '2016-05-07') + 1)