A mildly annoying SQL question

[Rawwr]

I need to calculate how many months are remaining in a set period and I don't think my brain is working very well today.

The period runs from 1st June to 31st May each year.

I want it the query to return the number of whole months remaining in the period.

If the query was run today, it would count; Aug, Sep, Oct, Nov, Dec, Jan, Feb, Mar, Apr, May to return 10 (AS tinyint) months. It wouldn't count July as we're already into it.

At the moment, I'm feeling like I want to scoop out my own eyeballs and set fire to them as I'm sure there's a really simple trick I'm missing.

[illmonkey]

+1

[robbieduncan]

What SQL dialect? Transact-SQL has datediff that does most of what you want

[Rawwr]

robbieduncan said:

What SQL dialect? Transact-SQL has datediff that does most of what you want

It's T-SQL and datediff does 99% of the work, it's the last 1% that's bothering me.

[robbieduncan]

I don't have easy access to a T-SQL environment right now convert(tinyint,floor(datediff(month,<date1 as datetime>,<date2 as datetime>))) looks like it would do what you want?

[Planet Claire]

illmonkey said:

+1

Yes, surely this is the answer, isn't it?

[wiggy001]

Something like this?

SELECT CAST(GETDATE() AS DATE) AS TODAY, CAST(CAST(YEAR(GETDATE()) AS CHAR(4)) + '-05-31' AS DATE) AS PERIOD_END, DATEDIFF(MONTH,GETDATE(), CAST(CAST(YEAR(GETDATE()) AS CHAR(4)) + '-05-31' AS DATE)) AS MONTHS_REMAINING
WHERE MONTH(GETDATE()) <= 5
UNION
SELECT CAST(GETDATE() AS DATE), CAST(CAST(YEAR(GETDATE())+1 AS CHAR(4)) + '-05-31' AS DATE), DATEDIFF(MONTH,GETDATE(), CAST(CAST(YEAR(GETDATE())+1 AS CHAR(4)) + '-05-31' AS DATE))
WHERE MONTH(GETDATE()) > 5

[Rawwr]

wiggy001 said:

Something like this?

SELECT CAST(GETDATE() AS DATE) AS TODAY, CAST(CAST(YEAR(GETDATE()) AS CHAR(4)) + '-05-31' AS DATE) AS PERIOD_END, DATEDIFF(MONTH,GETDATE(), CAST(CAST(YEAR(GETDATE()) AS CHAR(4)) + '-05-31' AS DATE)) AS MONTHS_REMAINING
WHERE MONTH(GETDATE()) <= 5
UNION
SELECT CAST(GETDATE() AS DATE), CAST(CAST(YEAR(GETDATE())+1 AS CHAR(4)) + '-05-31' AS DATE), DATEDIFF(MONTH,GETDATE(), CAST(CAST(YEAR(GETDATE())+1 AS CHAR(4)) + '-05-31' AS DATE))
WHERE MONTH(GETDATE()) > 5

That appears to work. Star

[zippy3x]

the formula

(17 - month) MOD 12

should give you the correct answer. 17 being 12 + 5 (May)

quick test run :

Month : 1 - remaining : 4
Month : 2 - remaining : 3
Month : 3 - remaining : 2
Month : 4 - remaining : 1
Month : 5 - remaining : 0
Month : 6 - remaining : 11
Month : 7 - remaining : 10
Month : 8 - remaining : 9
Month : 9 - remaining : 8
Month : 10 - remaining : 7
Month : 11 - remaining : 6
Month : 12 - remaining : 5

[wiggy001]

Rawwr said:

wiggy001 said:

Something like this?

SELECT CAST(GETDATE() AS DATE) AS TODAY, CAST(CAST(YEAR(GETDATE()) AS CHAR(4)) + '-05-31' AS DATE) AS PERIOD_END, DATEDIFF(MONTH,GETDATE(), CAST(CAST(YEAR(GETDATE()) AS CHAR(4)) + '-05-31' AS DATE)) AS MONTHS_REMAINING
WHERE MONTH(GETDATE()) <= 5
UNION
SELECT CAST(GETDATE() AS DATE), CAST(CAST(YEAR(GETDATE())+1 AS CHAR(4)) + '-05-31' AS DATE), DATEDIFF(MONTH,GETDATE(), CAST(CAST(YEAR(GETDATE())+1 AS CHAR(4)) + '-05-31' AS DATE))
WHERE MONTH(GETDATE()) > 5

That appears to work. Star

No probs. As an ERP developer/tech manager I've almost always got an MS SQL session open and your challenge caught me at the right time!