none
How to Convert the DateTime Value into the Fiscal Year ???????

    Question

  • I have a table weekendsummary , In this there is a field "MeetingDate" .

    I want to write a T-SQl Code which gives me the Fiscal Year On the Basis of MeetingDate

    i.e. if Meeting Date is --"2013-04-26 00:00:00.000" Then Fiscal Year Would be "2012-2013"

    OR If meeting date is "2012-10-19 00:00:00.000"  Then Fiscal Year Would be "2012-2013"

    same for all the data in this table

    Please suggest the T-SQL Code for this ????

    Monday, May 20, 2013 4:02 PM

Answers

  • Hi Guys !!

    I found a simple solution for this. As I want to show the data of previous 10 Fiscal Year .

    So the T-SQL which works for me is this :


    SELECT
     MeetingDate
    ,MeetingYear
    ,MeetingDateTime
    ,City
    ,Case WHEN MeetingDate between '2013-07-01' and '2014-06-30' THEN '2013-2014'
          WHEN MeetingDate between '2012-07-01' and '2013-06-30' THEN '2012-2013'
          WHEN MeetingDate between '2011-07-01' and '2012-06-30' THEN '2011-2012'
          WHEN MeetingDate between '2010-07-01' and '2011-06-30' THEN '2010-2011'
          WHEN MeetingDate between '2009-07-01' and '2010-06-30' THEN '2009-2010'
          WHEN MeetingDate between '2008-07-01' and '2009-06-30' THEN '2008-2009'
          WHEN MeetingDate between '2007-07-01' and '2008-06-30' THEN '2007-2008'
          WHEN MeetingDate between '2006-07-01' and '2007-06-30' THEN '2006-2007'
          WHEN MeetingDate between '2005-07-01' and '2006-06-30' THEN '2005-2006'
          WHEN MeetingDate between '2004-07-01' and '2005-06-30' THEN '2004-2015'
    END [Fiscal Year]
    FROM WeekEndSummary     

    • Marked as answer by Wazid Ali Wednesday, May 22, 2013 9:08 AM
    Wednesday, May 22, 2013 8:39 AM
  • You may try the below: (Hope this will help you too)

    Declare @s Date ='2012-6-26 00:00:00.000'
    Select Cast(DatePart(YEar,DATEADD(Month,-6,@s)) AS Varchar(4)) + '-' + Cast((DatePart(YEar,DATEADD(Month,-6,@s))+1) AS Varchar(4))
    SELECT
     MeetingDate
    ,MeetingYear
    ,MeetingDateTime
    ,City
    ,Cast(DatePart(YEar,DATEADD(Month,-6,MeetingDate)) AS Varchar(4)) + '-' 
    + Cast((DatePart(YEar,DATEADD(Month,-6,MeetingDate))+1) AS Varchar(4))
    as [Fiscal Year]
    FROM WeekEndSummary    



    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.



    • Edited by Latheesh NKMVP Wednesday, May 22, 2013 8:50 AM
    • Proposed as answer by Ravikanth R Wednesday, May 22, 2013 1:08 PM
    • Marked as answer by Wazid Ali Thursday, May 23, 2013 8:26 AM
    Wednesday, May 22, 2013 8:46 AM

All replies

  • Hi -

    In similar sort of requirements, I've usually included a FiscalYear, FiscalMonth, etc. type of column in my table so that I didn't have to calculate it on-the-fly.

    Without that, you would need some sort of lookup or set of data that has the date range per fiscal year and then run your SELECT or UPDATE from your weekendsummary table.


    - will

    Monday, May 20, 2013 4:12 PM
  • Will hints at a lookup table; use a lookup table.  The key/important date field is the transaction date, or the date when the meeting occurs.  Additional fields can be added for reporting: calendar date, fiscal date, Semester, Quarter, Month, Week...

    the special calendar (fiscal year) dates should NOT be computed columns.  It is possible that you currently have a Calendar Year Jan-Dec and Fiscal Year Apr-Mar or Oct-Sep and someone will want to change your fiscal year to match the calendar year...

    Monday, May 20, 2013 4:23 PM
  • Hi Will,

    We can add that column which you have suggested ? so what is the T-SQL Code for this if We an add the Fiscal Month column in this ????

    Please suggest ..

    Monday, May 20, 2013 5:46 PM
  • Monday, May 20, 2013 7:51 PM
    Moderator
  • Hi Guys !!

    I found a simple solution for this. As I want to show the data of previous 10 Fiscal Year .

    So the T-SQL which works for me is this :


    SELECT
     MeetingDate
    ,MeetingYear
    ,MeetingDateTime
    ,City
    ,Case WHEN MeetingDate between '2013-07-01' and '2014-06-30' THEN '2013-2014'
          WHEN MeetingDate between '2012-07-01' and '2013-06-30' THEN '2012-2013'
          WHEN MeetingDate between '2011-07-01' and '2012-06-30' THEN '2011-2012'
          WHEN MeetingDate between '2010-07-01' and '2011-06-30' THEN '2010-2011'
          WHEN MeetingDate between '2009-07-01' and '2010-06-30' THEN '2009-2010'
          WHEN MeetingDate between '2008-07-01' and '2009-06-30' THEN '2008-2009'
          WHEN MeetingDate between '2007-07-01' and '2008-06-30' THEN '2007-2008'
          WHEN MeetingDate between '2006-07-01' and '2007-06-30' THEN '2006-2007'
          WHEN MeetingDate between '2005-07-01' and '2006-06-30' THEN '2005-2006'
          WHEN MeetingDate between '2004-07-01' and '2005-06-30' THEN '2004-2015'
    END [Fiscal Year]
    FROM WeekEndSummary     

    • Marked as answer by Wazid Ali Wednesday, May 22, 2013 9:08 AM
    Wednesday, May 22, 2013 8:39 AM
  • You may try the below: (Hope this will help you too)

    Declare @s Date ='2012-6-26 00:00:00.000'
    Select Cast(DatePart(YEar,DATEADD(Month,-6,@s)) AS Varchar(4)) + '-' + Cast((DatePart(YEar,DATEADD(Month,-6,@s))+1) AS Varchar(4))
    SELECT
     MeetingDate
    ,MeetingYear
    ,MeetingDateTime
    ,City
    ,Cast(DatePart(YEar,DATEADD(Month,-6,MeetingDate)) AS Varchar(4)) + '-' 
    + Cast((DatePart(YEar,DATEADD(Month,-6,MeetingDate))+1) AS Varchar(4))
    as [Fiscal Year]
    FROM WeekEndSummary    



    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.



    • Edited by Latheesh NKMVP Wednesday, May 22, 2013 8:50 AM
    • Proposed as answer by Ravikanth R Wednesday, May 22, 2013 1:08 PM
    • Marked as answer by Wazid Ali Thursday, May 23, 2013 8:26 AM
    Wednesday, May 22, 2013 8:46 AM
  • Hi Latheesh ,

    Thanks a lot for Reply . I really wanna exactly this ..

    Wednesday, May 22, 2013 9:13 AM
  • A simpler method is:

    CASE WHEN MONTH(MeetingDate) > 6 THEN YEAR(MeetingDate)+1 ELSE YEAR(MeetingDate) END

    However, you will be sorry with this method when the account dept changes to calendar year in 2015.  A calendar table is better and only has to be calculated once.

    Wednesday, May 22, 2013 1:50 PM
    Moderator
  • You have an error: one if your fiscal years is 11 years long.

    Wednesday, May 22, 2013 2:08 PM