Fiscal year to date in SQL

Not knowing your field names, a query could go as follows:

DECLARE @FiscalYearEnd  datetime
SET @FiscalYearEnd = '2015-06-30'

SELECT  CONVERT(varchar(7), t.MyDate, 23)   AS FiscalMonth,  -- reduces yyyy-mm-dd to yyyy-mm
        SUM(t.MyAmount)                     AS FiscalAmount
FROM    MyTable t WITH (NOLOCK)
WHERE   CONVERT(date, t.MyDate) BETWEEN DATEADD(day, 1, DATEADD(year, -1, @FiscalYearEnd)) AND @FiscalYearEnd
GROUP BY CONVERT(varchar(7), t.MyDate, 23)

Select allOpen in new window

This makes a lot of assumptions. Seeing your dates were listed month by month, I'm assuming you want to SUM some amounts by fiscal month. The variable @FiscalYearEnd is one example of how to pass your fiscal year end value.

Sorry,

I am not doing any calculation in this table. Ex:

I have a table called, "Input_5929" where a specific column called NewTimeDateFormat which has the above date values.

What i need is, select distinct top 1 NewTimeDateformat from Input_5929 order by NewTimeDateFormat desc

which will give me one value, lets say, "2015-05-01 00:00:00"

Then my fiscal year to date query should give me the below value:

2015-05-01 00:00:00
2015-04-01 00:00:00
2015-03-01 00:00:00
2015-02-01 00:00:00
2015-01-01 00:00:00
2014-12-01 00:00:00
2014-11-01 00:00:00
2014-10-01 00:00:00
2014-09-01 00:00:00
2014-08-01 00:00:00
2014-07-01 00:00:00

but if my top 1 distinct value gives me "2015-07-01" Then fiscal year to date will give me "2015-07-01" only.

dsacker - Based on your query, I managed to get what i need. But i need the @FiscalyearEnd date should be dynamic based on my top 1 distinct query. possible?

DECLARE @FiscalYearEnd  datetime
SET @FiscalYearEnd = '2015-06-30'

SELECT  CONVERT(datetime, t.NewTimeDateFormat, 23)   AS FiscalMonth,
(Left(DATENAME(month, CONVERT(datetime, t.NewTimeDateFormat, 23)),3)+'-'+Right(Year(CONVERT(datetime, t.NewTimeDateFormat, 23)),2)) 
AS 'NewTimeCheck' FROM    Input_5929 t WITH (NOLOCK)
WHERE   CONVERT(date, t.NewTimeDateFormat) BETWEEN 
DATEADD(day, 1, DATEADD(year, -1, @FiscalYearEnd)) AND @FiscalYearEnd
GROUP BY CONVERT(datetime, t.NewTimeDateFormat, 23) order by FiscalMonth desc

Select allOpen in new window

Gotcha. I'm working out the query now. One moment.

I had to make a small correction to my code above.

it says 'Date' is not a recognized built-in function name.

I had to make a small correction.

Replace just these lines:

SET @FiscalDateBeg = CONVERT(datetime, CONVERT(varchar(5), YEAR(@FiscalDateEnd) - 1) + '-07-01')
IF @FiscalDateEnd >= CONVERT(varchar(5), YEAR(GETDATE())) + '-07-01'
    SET @FiscalDateBeg = CONVERT(varchar(5), YEAR(@FiscalDateEnd)) + '-07-01'

I updated your code with my query, but it gives me full months(12 months). doesnt stop at July.

DECLARE @FiscalDateBeg  datetime,
        @FiscalDateEnd  datetime

SELECT TOP 1 @FiscalDateEnd = NewTimeDateFormat
FROM   Input_5929 WITH (NOLOCK)
ORDER BY NewTimeDateFormat DESC

SET @FiscalDateBeg = CONVERT(datetime, CONVERT(varchar(5), YEAR(@FiscalDateEnd) - 1) + '-07-01')
IF @FiscalDateEnd >= CONVERT(varchar(5), YEAR(GETDATE())) + '-07-01'
    SET @FiscalDateBeg = CONVERT(varchar(5), YEAR(@FiscalDateEnd)) + '-07-01'

SELECT  CONVERT(datetime, t.NewTimeDateFormat, 23)   AS FiscalMonth,
(Left(DATENAME(month, CONVERT(datetime, t.NewTimeDateFormat, 23)),3)+'-'+Right(Year(CONVERT(datetime, t.NewTimeDateFormat, 23)),2)) 
AS 'NewTimeCheck' FROM    Input_5929 t WITH (NOLOCK)
WHERE   CONVERT(date, t.NewTimeDateFormat) BETWEEN 
DATEADD(day, 1, DATEADD(year, -1, @FiscalDateEnd)) AND @FiscalDateEnd
GROUP BY CONVERT(datetime, t.NewTimeDateFormat, 23) order by FiscalMonth desc

Select allOpen in new window

Ouput

FiscalMonth	NewTimeCheck
2015-03-01 00:00:00.000	Mar-15
2015-02-01 00:00:00.000	Feb-15
2015-01-01 00:00:00.000	Jan-15
2014-12-01 00:00:00.000	Dec-14
2014-11-01 00:00:00.000	Nov-14
2014-10-01 00:00:00.000	Oct-14
2014-09-01 00:00:00.000	Sep-14
2014-08-01 00:00:00.000	Aug-14
2014-07-01 00:00:00.000	Jul-14
2014-06-01 00:00:00.000	Jun-14
2014-05-01 00:00:00.000	May-14
2014-04-01 00:00:00.000	Apr-14

Select allOpen in new window

Gotcha.., changed the between date's. Its working like a charm.

DECLARE @FiscalDateBeg  datetime,
        @FiscalDateEnd  datetime

SELECT TOP 1 @FiscalDateEnd = NewTimeDateFormat
FROM   Input_5929 WITH (NOLOCK)
ORDER BY NewTimeDateFormat DESC

SET @FiscalDateBeg = CONVERT(datetime, CONVERT(varchar(5), YEAR(@FiscalDateEnd) - 1) + '-07-01')


IF @FiscalDateEnd >= CONVERT(varchar(5), YEAR(GETDATE())) + '-07-01'
    SET @FiscalDateBeg = CONVERT(varchar(5), YEAR(@FiscalDateEnd)) + '-07-01'

SELECT  CONVERT(datetime, t.NewTimeDateFormat, 23)   AS FiscalMonth,
(Left(DATENAME(month, CONVERT(datetime, t.NewTimeDateFormat, 23)),3)+'-'+Right(Year(CONVERT(datetime, t.NewTimeDateFormat, 23)),2)) 
AS 'NewTimeCheck' FROM    Input_5929 t WITH (NOLOCK)
WHERE   CONVERT(date, t.NewTimeDateFormat) BETWEEN @FiscalDateBeg and 
--DATEADD(day, 1, DATEADD(year, -1, @FiscalDateEnd)) AND 
@FiscalDateEnd
GROUP BY CONVERT(datetime, t.NewTimeDateFormat, 23) order by FiscalMonth desc

Select allOpen in new window

I was typing that fix when I saw the notification of a new email from you, and thought, "Bet he figured it out." :)

Manju - I just kick out an article that deals specifically with Fiscal calendar planning --> SQL Server Calendar Table:  Fiscal Years.  Since you've already accepted an answer here, if the article helps you out then click on the 'Good Article' button and provide some feedback.  Thanks.