Robert Meyer Robert Meyer - 1 month ago 6
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

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)