SQL to separate YYYY MM to fiscal year

Question

I have a column which states month and year YYYY MM. I've separated those into two columns (Year and Month). The problem is, the year is the calendar year whereas ideally I need the fiscal year I use (Apr 01 to Mar 31 - This will never change).

Other solutions I've seen are based on date format, whereas my original column is string.

I need a statement that returns the fiscal year for my new year column instead of the calendar year.

My current statement is:

Select Month,
parsename(replace(Month,' ','.'),1) as MonthM,
parsename(replace(Month,' ','.'),2) as Year
FROM TblTrade

Which works to separate the columns.

So expected results would be for example: Feb 15 becomes Feb and 2015. Apr 15 becomes Apr and 2016.

Please advise.

Answer 1

Sql server:

declare @date datetime = getdate();
select 
(YEAR(DATEADD(Month,-((DATEPART(Month,@date)+5) %12),@date))) AS Financial_Year

Assuming April is month 1

Answer 2

Try this

    select case 
             when to_char(to_date(column_name,'yyyy mm'),'mm') between 01 and 03 
                  then to_char(trunc(to_date(column_name,'yyyy mm')),'yyyy')-1
             else to_number(to_char(trunc(to_date(column_name,'yyyy mm')),'yyyy')) end 
    fiscal_year
    from table_name

I'm using oracle db This will work when column is string and has valid data i.e date in format like yyyy mm

Answer 3

Since you've read those other articles (you should really mention what research you've done in your question) and you're still having problems, I've had a play for you.

If I understand correctly, you have a varchar with YYYY MM eg

2015 01
2015 02
2015 03
2015 04

etc And you want

Jan 2014
Feb 2014
Mar 2014
Apr 2015

Here goes...

Setup some test data

    DROP TABLE IF EXISTS #Test;

    WITH Dates AS (
        SELECT CAST(GETDATE() AS DATE) AS Date
        UNION ALL
        SELECT DATEADD(MONTH, -1, Date) FROM Dates
        WHERE Date > '20140101'
    )
    SELECT DISTINCT
        CONVERT(VARCHAR(4), YEAR(Date)) + ' ' +RIGHT(CONVERT(VARCHAR(6), Date, 112), 2) YearMonth
    INTO #Test 
     FROM Dates
    OPTION (MAXRECURSION 0);

    SELECT * FROM #Test

YearMonth
---------
2013 12
2014 01
2014 02
2014 03
2014 04
2014 05
etc

Find Fiscal Year

    SELECT 
        LEFT(YEARMONTH, 4) Year
        ,RIGHT(YEARMONTH, 2) Month
        ,LEFT(DATENAME(MONTH , DateAdd( month , CONVERT(INT,RIGHT(YEARMONTH, 2)) , -1 )), 3) MonthName
        ,IIF(CONVERT(INT, RIGHT(YEARMONTH, 2)) >= 4, CONVERT(INT,LEFT(YEARMONTH, 4)), CONVERT(INT,LEFT(YEARMONTH, 4)-1 )) FiscalYear
    FROM #TEST

    Year Month MonthName FiscalYear
    ---- ----- --------- -----------
    2013 12    Dec       2013
    2014 01    Jan       2013
    2014 02    Feb       2013
    2014 03    Mar       2013
    2014 04    Apr       2014
    2014 05    May       2014
    2014 06    Jun       2014
    etc

You could put the year/month parsing in a sub query just to make the code cleaner and some of the nasty formatting could be replaced with FORMAT since you're on 2012.

Hope this is what you're after and helps.

Answer 4

Since you included the Tableau tag, I'll describe the Tableau approach -- which is a little different than the other answers since you tend to specify what you want to Tableau, and let its driver generate the necessary SQL for your database.

First, it will work best if you have a single field that has datatype DATE instead of separate fields for month and year.

You can then roll up dates to the nearest year, month, day etc (actually truncating to the beginning of the period) or extract specific parts of dates year, month, day etc as needed for grouping/display.

The added benefit of working with a true DATE datatype is that you can tell Tableau the beginning of your fiscal year for each data source, and it will sort dates appropriately. Just right click on a data source and set the date properties. You can also set the start of the week and the date format.