how to find max dates of months in a financial year ( april to march )

Question

THe input will be month and year

 

i.e., for a input of 5,2008 ( april 2008)

 

the output should be

 

30th april 2008

31st may 2008

 

and if input is 3,2009

it should be

 

april 2008 to march 2009 max dates

 

thanks in advance

Answer 1

Try computing the last day of a given month is by computing the first day of the next month and subtracting off one day.  One suggestion for doing this is to use a calendar table.  A writeup for a calendar table can be found here.

 

Here is one method without using a calendar table:

 

Code Snippet

declare @month tinyint  set @month = 3
declare @year smallint  set @year = 2009

 

select
  cast(day(dateadd(mm, n, baseDate) - 1) as varchar(2))
    + case day(dateadd(mm, n, baseDate) - 1)
        when 31 then 'st '
        else 'th '
      end
    + datename(mm, dateadd(mm, n, baseDate) - 1)
    + ' ' + cast(year(dateadd(mm, n, baseDate) - 1) as char(4))
  as End_Of_Month
from
( select
    @Month as theMonth,
    @Year as theYear,
    dateadd(mm, datediff( mm, 0,
              case when @Month > 3
                     then cast(@Year as char(4))
                   else cast(@Year-1 as char(4))
              end + '0401'), 0)
    as baseDate,
    case when @Month > 3
           then @Month - 3
         else @Month + 9
    end as monthCount
) dates
join
( select 1 as n union all select 2 as n union all select 3 union all
  select  4 union all select  5 union all select  6 union all
  select  7 union all select  8 union all select  9 union all
  select 10 union all select 11 union all select 12
) as numbers
  on n <= monthCount

 

/* -------- Sample Output: --------
End_Of_Month
----------------------------------------
30th April 2008
31st May 2008
30th June 2008
31st July 2008
31st August 2008
30th September 2008
31st October 2008
30th November 2008
31st December 2008
31st January 2009
28th February 2009
31st March 2009
*/

 

 

The derived table that is used for the JOIN is a substitute for a table of numbers.  If you already have a table of numbers or something similar the JOIN will be a bit simpler.

Answer 2

A few things to note here.  One you are much better off doing this type of functionality with a numbers or calendar table.  Secondly, I would not break the input parameters into month and year.  This only complicates the query and does not give you any performance enhancement.  You should pass in an end date and calulate the range based on that single value. This way you are using dates in the native format and require less converting manipulation to get the piece of the string you are after.

 

Note for the non numbers table solution I dynamically picked the starting date, you can incorporate that logic into the numbers table solution if you so choose.

 

Here is a numbers table solution:

Code Snippet

declare @EndDt DATETIME,

        @StartDt DATETIME,

        @NbrMonths INT

           

SET @StartDt = '2008-04-30'

SET @EndDt = '2009-04-1'

SET @NbrMonths = DATEDIFF(MONTH,@StartDt,@EndDt)

 

;WITH cte

AS

(

SELECT DateAdd(second,-1,DateAdd(month,DateDiff(month,0,@StartDt)+N,0)) AS Dt

FROM [dbo].[Numbers]

WHERE N <= @NbrMonths

)

SELECT

      STUFF(

            CONVERT(VARCHAR(10),Dt,103)

            ,3

            ,4

            ,CASE WHEN DATEPART(DAY,Dt) = 31 THEN

                  'st'

             ELSE 'th'

             END +

             SPACE(1) +

             DATENAME(MONTH,Dt) +

             SPACE(1)

            )

FROM cte

ORDER BY Dt ASC

 

 

 

 

Here is the non calendar solution (SQL 2005 + only)

Code Snippet

declare @EndDt DATETIME

SET @EndDt = DateAdd(second,-1,DateAdd(month, DateDiff(month, 0'2009-03-01')+1,0))

 

;WITH cte

AS

(

SELECT CONVERT(DATETIME,'04/30/' + CONVERT(CHAR(4),DATEPART(YEAR,GETDATE()))) AS baseDt

UNION ALL

SELECT DateAdd(second,-1,DateAdd(month, DateDiff(month, 0,BaseDt+1)+1,0))

FROM Cte

WHERE DateAdd(second,-1,DateAdd(month, DateDiff(month, 0,BaseDt+1)+1,0)) <= @EndDt

)

SELECT

      STUFF(

            CONVERT(VARCHAR(10),BaseDt,103)

            ,3

            ,4

            ,CASE WHEN DATEPART(DAY,BaseDt) = 31 THEN

                  'st'

             ELSE 'th'

             END +

             SPACE(1) +

             DATENAME(MONTH,BaseDt) +

             SPACE(1)

            )

FROM cte

ORDER BY BaseDt ASC

 

 

 

Answer 3

Can you be more specific in the request.  I"m a bit confused....

 

In your first example, the output dates are about 30 days apart.  In your second example, the output dates are almost 12 months apart. 

 

I'm a bit confused, but I think there is a very quick and easy way to do what you want...I just need to understand better.

 

Answer 4

5, 2008....isn't that May 2008?  Why is "5" = April?

 

In any event, a little date math, and you've got just about everything you need.  Some version of this should work well for you:

 

 

Code Snippet

declare @iMonth int, @iYear int

set @iMonth = 5

set @iYear = 2008

 

 

 

set @iMonth = @iMonth + ((@iYear-1900) * 12) - 1

select dateadd(d,-1,dateadd(m, @iMonth + [A],0))

from (

select top 12 Row_Number() over (order by Object_ID) as [A]

from sys.objects

) as [A]

 

 

RESULTS

2008-05-31 00:00:00.000
2008-06-30 00:00:00.000
2008-07-31 00:00:00.000
2008-08-31 00:00:00.000
2008-09-30 00:00:00.000
2008-10-31 00:00:00.000
2008-11-30 00:00:00.000
2008-12-31 00:00:00.000
2009-01-31 00:00:00.000
2009-02-28 00:00:00.000
2009-03-31 00:00:00.000
2009-04-30 00:00:00.000

 

Answer 5

rusag2 wrote:

5, 2008....isn't that May 2008?  Why is "5" = April?   In any event, a little date math, and you've got just about everything you need.  Some version of this should work well for you:     Code Snippet declare @iMonth int, @iYear int set @iMonth = 5 set @iYear = 2008       set @iMonth = @iMonth + ((@iYear-1900) * 12) - 1 select dateadd(d,-1,dateadd(m, @iMonth + [A],0)) from ( select top 12 Row_Number() over (order by Object_ID) as [A] from sys.objects ) as [A]     RESULTS 2008-05-31 00:00:00.000 2008-06-30 00:00:00.000 2008-07-31 00:00:00.000 2008-08-31 00:00:00.000 2008-09-30 00:00:00.000 2008-10-31 00:00:00.000 2008-11-30 00:00:00.000 2008-12-31 00:00:00.000 2009-01-31 00:00:00.000 2009-02-28 00:00:00.000 2009-03-31 00:00:00.000 2009-04-30 00:00:00.000

Rusag2,

 

I believe April is when the new fiscal year begins.  So the starting point will always be the last day of April...  I think.  The parameter passed in dictates when the month end values stop, so 5,2008 says to stop on May 2008.  Thus the ultimate purpose of the query is to get all month end days for a given fiscal year or a range of dates with the current fiscal year.

Answer 6

if i pass 3,2008 to this query it give you dates from march 2008 to feb 2009

 

but it should be other way around

 

i.e., if i pass 3,2008 the output should be

 

april 2007 to march  2008

Answer 7

Hi Mate,
Please try this.

select

 

dateadd(mm,3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)) as FirstDay_CurrentFY,

 

dateadd(yy,1,dateadd(mm,3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))-1) as LastDay_CurrentFY,

 

dateadd(yy,-1,dateadd(mm,3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))) as FirstDay_PriorFY,

 

dateadd(yy,-1,dateadd(yy,1,dateadd(mm,3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))-1)) as LastDay_PriorFY