none
How to convert Calendar Month Year to Fiscal Month Year

    Question

  •  

    I have a calendar date field in my  table. I want to add one more field (Fiscal Month/Year) in my table on basis of Calendar Date filed.

    Our fiscal year starts from October i.e. Calendar Month/ Year : 10/2007  = Fiscal Month/Year: 01/2008

     

    The end result which i am looking for :

     

    Calendar Month Year Fiscal Month Year
    10/2007 01/2008
    11/2007 02/2008
    12/2007 03/2008
    01/2008 04/2008
    02/2008 05/2008
    03/2008 06/2008
    04/2008 07/2008
    05/2008 08/2008
    06/2008 09/2008
    07/2008 10/2008
    08/2008 11/2008
    09/2008 12/2008
    10/2008 01/2009
    11/2008 02/2009
    12/2008 03/2009

     

    Thursday, May 08, 2008 6:01 PM

Answers

  • SQL does not really store "Partial Dates" (Month/Year without Day/Time).

     

    I recommend that you change the DataType in your [Calendar Month Year]  field to SmallDateTime and then store the "StartOfCalendarMonth":

     

    10/01/2007

    11/01/2007

    12/01/2007 etc etc

     

    THen, add a column like this:

     

    alter table <MyTable>  ADD [StartOfFiscalMonth] as DateAdd(m,3, [Calendar Month Year])

     

    Since your fiscal month is always three months ahead of the calendar month, a calculated column will work well here.  Also, when stored as a DateTime, you can sort these and do date manipulation much more easily than if the data is stored as strings.

     

    Thursday, May 08, 2008 6:20 PM
  • Thank you it work with small adjustment to convert calendar date into fiscal date

     

    Modify / Design table Add new column Fiscal_date then in compute add this formula...

     

    Code Snippet

    (CONVERT([nvarchar](10),dateadd(month,(3),[CALENDAR_DATE]),(101)))

     

     

    Thursday, May 08, 2008 7:52 PM

All replies

  • SQL does not really store "Partial Dates" (Month/Year without Day/Time).

     

    I recommend that you change the DataType in your [Calendar Month Year]  field to SmallDateTime and then store the "StartOfCalendarMonth":

     

    10/01/2007

    11/01/2007

    12/01/2007 etc etc

     

    THen, add a column like this:

     

    alter table <MyTable>  ADD [StartOfFiscalMonth] as DateAdd(m,3, [Calendar Month Year])

     

    Since your fiscal month is always three months ahead of the calendar month, a calculated column will work well here.  Also, when stored as a DateTime, you can sort these and do date manipulation much more easily than if the data is stored as strings.

     

    Thursday, May 08, 2008 6:20 PM
  • Thank you it work with small adjustment to convert calendar date into fiscal date

     

    Modify / Design table Add new column Fiscal_date then in compute add this formula...

     

    Code Snippet

    (CONVERT([nvarchar](10),dateadd(month,(3),[CALENDAR_DATE]),(101)))

     

     

    Thursday, May 08, 2008 7:52 PM