1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

SQL Dates - Financial Year

Discussion in 'HTML, Graphics & Programming' started by Ciphon, 10 Sep 2010.

  1. Ciphon

    Wise Guy

    Joined: 9 Dec 2008

    Posts: 2,341

    Location: Hampshire

    Hi All,

    I'm looking for a way to figure out the current financial year in SQL.

    I'm creating a rolling report thats going to be emailed out via report centre once a month. If it were executed today, it should contain all details from '2010-04-01' and '2011-03-31'.

    I want to avoid having to updating the dates manually in the report every year, but can't figure out how I'd work out the current financial year.

    I've tried every combination of DATEPART, DATENAME, DATEDIFF, and GETDATE I can think of.. but I must be missing something.

    Any ideas?

    Thanks
     
  2. Redgie

    Wise Guy

    Joined: 18 Nov 2008

    Posts: 2,430

    Location: Liverpool

    Can't you just compare the current year with the year in the SQL statement, and add "WHERE Date > '".$CurYear>"'"

    With the following before hand: $CurYear = $Year."-04-01"

    And have $Year assigned as the first 4 characters of DATE?

    Sorry I can't write anything up properly, but the OcUK input box is hardly the best place to code ^^
     
  3. Ciphon

    Wise Guy

    Joined: 9 Dec 2008

    Posts: 2,341

    Location: Hampshire

    Hah.. Agreed! I've actually just figured it out.. I actually need the last day of April (Just the way it's stored in this particular DB).

    The way I did it:

    SELECT CONVERT(DATE,DATEADD(D, - DATEPART(D, GETDATE()), DATEADD(M, 5, DATEADD(M, - DATEPART(M, GETDATE()), GETDATE()))))
     
    Last edited: 10 Sep 2010
  4. MFrost

    Gangster

    Joined: 20 Feb 2007

    Posts: 131

    I use Oracle not Microsoft SQL. If i was looking at this problem I would probably make 2 functions. 1 to get the year start date, and 1 to get the year end date - then use a between statement. Something like below (although this definately wont work out of the tin - written in notepad :p).

    Code:
    Function get_year_start(sysdate)
    
    DECLARE @date DATETIME
    
        Case when month(sysdate) > 3 then   
            SET @date = year(sysdate) + '-04-01'
        ELSE
            SET @date = (year(sysdate)-1) + -04-01'        
        END;
        Return @date;
    End function
    
    
    
    Function get_year_end(sysdate)
    
    DECLARE @date DATETIME
    
        Case when month(sysdate) > 3 then   
            SET @date = (year(sysdate)+1) + '-03-31'
        ELSE
            SET @date = year(sysdate)-1 + -03-31'        
        END;
        Return @date;
    End function
    
    
    SELECT
    'your stuff
    FROM
    'your table
    WHERE
    'your date between get_year_start(sysdate) and get_year_end(sysdate)
    I sometimes wish i could be bothered to set up an SQL server at home...but that's probably a bit sad. :(
     
  5. Ciphon

    Wise Guy

    Joined: 9 Dec 2008

    Posts: 2,341

    Location: Hampshire

    I have one :( lol.

    FYI for those who do need the first day of the month, rather than the last:

    SELECT CONVERT(DATE, DATEADD(D, 1, DATEADD(D, - DATEPART(D, GETDATE()), DATEADD(M, 4, DATEADD(M, - DATEPART(M, GETDATE()), GETDATE())))))
     
  6. Ciphon

    Wise Guy

    Joined: 9 Dec 2008

    Posts: 2,341

    Location: Hampshire

    Actually the above is incorrect.

    If I used the above, then when we were between Jan 1st 2011 and March 31st 2011, it would have had a fit and not pulled any data, because it would be looking for April 2011.

    So I've gone for a CASE in my WHERE clause:


    MyDate >=
    CASE
    WHEN GETDATE() BETWEEN
    CONVERT(DATE, DATEADD(D, 1, DATEADD(D, - DATEPART(D, GETDATE()), DATEADD(M, 1, DATEADD(M, - DATEPART(M, GETDATE()), GETDATE())))))
    AND
    CONVERT(DATE, DATEADD(D, 1, DATEADD(M, -1, DATEADD(D, - DATEPART(D, GETDATE()), DATEADD(M, 5, DATEADD(M, - DATEPART(M, GETDATE()), GETDATE()))))))
    THEN CONVERT(DATE,DATEADD(YEAR, -1, DATEADD(D, - DATEPART(D, GETDATE()), DATEADD(M, 5, DATEADD(M, - DATEPART(M, GETDATE()), GETDATE())))))
    ELSE CONVERT(DATE,DATEADD(D, - DATEPART(D, GETDATE()), DATEADD(M, 5, DATEADD(M, - DATEPART(M, GETDATE()), GETDATE()))))
    END
     
  7. Conficker

    Associate

    Joined: 23 Feb 2012

    Posts: 1

    you can try below global script,

    DECLARE @StartDate DATETIME
    DECLARE @EndDate DATETIME

    SET @StartDate = DATEADD(dd,0, DATEDIFF(dd,0, DATEADD( mm, -(((12 + DATEPART(m, getDate())) - 4)%12), getDate() ) - datePart(d,DATEADD( mm, -(((12 + DATEPART(m, getDate())) - 4)%12),getDate() ))+1 ) )

    SET @EndDate = DATEADD(SS,-1,DATEADD(mm,12,@StartDate))


    SELECT @StartDate,@EndDate
     
  8. djkav

    Mobster

    Joined: 28 Aug 2006

    Posts: 2,979

    I didn't think that it made a difference how dates are stored if you use the ISO format 'YYYY-MM-DD"

    My pseudo code:
    Code:
    Is current month > March AND < January
    
    	currentFinancialYearStart = year(today) & "-APR-01"
    
    	currentFinancialYearEnd = year(today) + 1 & "-APR-01"
    	currentFinancialYearEnd = day(currentFinancialYearEnd) - 1  // To set last day in March.
    	
    Else
    
    	currentFinancialYearStart = year(today) - 1 & "-APR-01"
    
    	currentFinancialYearEnd = year(today) & "-APR-01"
    	currentFinancialYearEnd = day(currentFinancialYearEnd) - 1  // To set last day in March.
    	
     
    Last edited: 24 Feb 2012
  9. jellybeard999

    Sgarrista

    Joined: 7 Feb 2004

    Posts: 7,831

    Location: North East

    Digging this up from the depths as it seems relevant to the discussion... I hadn't thought to post here, but this was one of the top google results!

    I am relatively OK (for what I need to do) with Oracle SQL, however I'm less familiar with MS SQL and I'm struggling to come up with a query to return transactions between dates x and y, where x is 1st April 7 financial years ago and y is 31st Mar for the end of the current financial year

    In Oracle, this brings back the first date of the financial year 7 years ago, taking into account that in Jan-Mar it will return 2013, or 2014 in Apr-Dec (I think those are the right years, but you get the idea!)
    '01-Apr-' || (EXTRACT (YEAR FROM ADD_MONTHS (SYSDATE, -3))-7)

    I have managed to get the below working, but this will return 2014 if it is run any time in 2021, which isn't quite what I want
    CONCAT(YEAR(DATEADD(YY, -7, GETDATE())),'-04-01') and GETDATE()

    I hope that makes sense... can anyone assist or suggest an alternative method?

    I've been through the above posts, but I'm none the wiser :p ... in the meantime I'll have another read through in case something clicks!
     
    Last edited: 11 Jun 2021
  10. skyripper

    Wise Guy

    Joined: 19 Jul 2011

    Posts: 2,182

    Thinking it thru, how many years back you go depends on whether you are before or after 1st April. So you need a case or decode statement to work that out.

    Your From and To dates - are they always 1st April and today, or are they 1st april to 31st March?
     
  11. skyripper

    Wise Guy

    Joined: 19 Jul 2011

    Posts: 2,182

    You probably don't need all the TO_CHAR, TO_DATE crap if you're working with native oracle date formats.
    I struggle with dates unless they are DD/MM/YYYY [because thats what the app I look after generally uses]

    Code:
    SELECT TO_DATE(:1,'DD/MM/YYYY') DATE_TESTED
         , TO_DATE( '01/04/'||TO_CHAR(EXTRACT(YEAR from TO_DATE(:1,'DD/MM/YYYY'))),'DD/MM/YYYY' ) APRIL_THIS_YEAR,
     CASE WHEN ( TO_DATE(:1,'DD/MM/YYYY') < TO_DATE( '01/04/'||TO_CHAR(EXTRACT(YEAR from TO_DATE(:1,'DD/MM/YYYY'))),'DD/MM/YYYY' ))
          THEN 'Before'
          ELSE 'After'
      END WHERE_IN_YEAR,
     CASE WHEN ( TO_DATE(:1,'DD/MM/YYYY') < TO_DATE( '01/04/'||TO_CHAR(EXTRACT(YEAR from TO_DATE(:1,'DD/MM/YYYY'))),'DD/MM/YYYY' ))
          THEN ADD_MONTHS( TO_DATE( '01/04/'||TO_CHAR(EXTRACT(YEAR from TO_DATE(:1,'DD/MM/YYYY'))),'DD/MM/YYYY' ), (-8 * 12) )
          ELSE ADD_MONTHS( TO_DATE( '01/04/'||TO_CHAR(EXTRACT(YEAR from TO_DATE(:1,'DD/MM/YYYY'))),'DD/MM/YYYY' ), (-7 * 12) )
      END FY_BACK_SEVEN_BEGINS
    FROM DUAL
    ;
     
  12. skyripper

    Wise Guy

    Joined: 19 Jul 2011

    Posts: 2,182

    So if you feed it 01/03/2021, it will give you 01-Apr-2013.
    If you feed it 01/06/2021, it will give you 01-Apr-2014

    @jellybeard999
     
    Last edited: 11 Jun 2021
  13. jellybeard999

    Sgarrista

    Joined: 7 Feb 2004

    Posts: 7,831

    Location: North East

    Thanks @skyripper

    That seems very complicated compared to how it can be calculated in OracleSQL :s , which is basically deduct 3 months from the current date, which corrects the offset, then extract the year.

    I'm running through a program to import via ODBC and I've tried plugging in your formula and it doesn't seem to like it :( It's known to be finicky, but where would the table name feature in your script?

    I'm trying to use the below format and I'm assuming your code is the "Y"

    SELECT * FROM X WHERE Y

    Thanks again :)
     
    Last edited: 15 Jun 2021
  14. touch

    Capodecina

    Joined: 28 Oct 2006

    Posts: 11,940

    Location: Sufferlandria

    How about this:

    CAST(CONCAT(DATEPART(year,DATEADD(month, -3, GETDATE()))-7,'-04-01')as DateTime)
     
  15. jellybeard999

    Sgarrista

    Joined: 7 Feb 2004

    Posts: 7,831

    Location: North East

    @touch

    That's exactly what I was after! Thanks
     
  16. skyripper

    Wise Guy

    Joined: 19 Jul 2011

    Posts: 2,182

    The table name was DUAL, which in Oracle is a pretend table. So I guess you'd substitute in your own table name and values rather than the :1 stuff which is just bind variables. It'd also look a lot simpler when the TO_DATE and TO_CHAR function s are stripped out.
     
  17. skyripper

    Wise Guy

    Joined: 19 Jul 2011

    Posts: 2,182

    However touch's all in one function looks like a damn good bet too!
     
  18. Dolph

    Man of Honour

    Joined: 17 Oct 2002

    Posts: 49,930

    Location: Plymouth

    It's potentially worth exploring the idea of a date dimension of you have any influence on the design of the database/data warehouse.

    https://radacad.com/do-you-need-a-date-dimension

    Implemented well, it makes it very easy to handle all sorts of weird business date requirements without the user having to work out how to make SQL do it themselves.
     
  19. dazzerd

    Wise Guy

    Joined: 14 Mar 2007

    Posts: 1,619

    Location: Winchester

    Seems massively overcomplicated. In a bi environment you would just create a calendar table and join your fact table too it. No reason this shouldnt work in sql environment too. Just create csv witj primary key as start date to end date for every day. And then have flaf in the calender table specifying whether a date is in your business year. Then do an inner join in your query to the sql table and calender table.
     
  20. Ergates

    Gangster

    Joined: 24 Jun 2005

    Posts: 247

    Thread necromancy for this:
    "You probably don't need all the TO_CHAR, TO_DATE crap if you're working with native oracle date formats."
    Always always *always* mask your dates (in Oracle) when writing code (as opposed to just running a query to check something).