Getting Started with Adobe After Effects - Part 6: Motion Blur
A collection of quick technology learning tips from what people around you learn every day

Finding out the beginning of current financial year in TSQL

Feb 16 2012 12:00AM by Conficker   

This script shows how to find the beginning of current financial year (for countries using April 1 as the beginning) using TSQL.

SELECT 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 ) )

Read More..   [32134 clicks]

Published under: SQL Server Tips ·  ·  ·  · 


Conficker
230 · 1% · 195
2
 
1
 
 
 
0
Interesting
 
0
Forgotten



Submit

12  Comments  

    1. If you replace GETDATE() with the date of 2012-03-31 you get 2012-03-31 as result. Even 2012-05-31 gives a result of 2012-03-31!
    2. If you use DATE instead of DATETIME for @Today, your query fails due to inconsistent data types.

    You tip above gives the wrong date in 854 cases for dates between 1910-01-31 and 2031-12-31 (an error ratio of 2%).

    Use this. It's safer, faster and shorter.

    SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000401', @Today) / 12 * 12, '19000401')

    commented on Feb 16 2012 6:47AM
    Peso
    100 · 2% · 527
  • Peso, neat one. And it even works with @today='2012-03-12 23:59:59.998' but not with '2012-03-31 23:59:59.999'. So a blink before the next day you are already in the next day. Doing a select convert(datetime,'2012-03-31 23:59:59.999') returns 2012-04-01 00.00.00.000. Did you blink? Yes, I know about the milisecond portion being imprecise.

    commented on Feb 16 2012 7:18AM
    dishdy
    16 · 10% · 3269
  • Thanks for noticed,

    Try this one

    declare @myDate datetime set @myDate ='2011-03-31' -- set @myDate =getDate()

    SELECT DATEADD(dd,0, DATEDIFF(dd,0, DATEADD( mm, -(((12 + DATEPART(m, @myDAte)) - 4)%12), @myDAte ) - datePart(d,DATEADD( mm, -(((12 + DATEPART(m, @myDAte)) - 4)%12),@myDAte ))+1 ) )

    commented on Feb 16 2012 7:27AM
    Conficker
    230 · 1% · 195
  • Dishdy, it does work even with "999" milliseconds, if you choose the correct datatype to begin with.

    DECLARE @Today DATETIME2(3) = '2012-03-31 23:59:59.999'

    SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000401', @Today) / 12 * 12, '19000401')

    Conficker, now it returns the correct date. But is it easier to read or understand? And as before, it doesn't work with the new DATE and DATETIME2 datatypes.

    -- SwePeso

    DATEADD(MONTH, DATEDIFF(MONTH, '19000401', @MyDate) / 12 * 12, '19000401')

    -- Conficker

    DATEADD(dd,0, DATEDIFF(dd,0, DATEADD( mm, -(((12 + DATEPART(m, @MyDate)) - 4)%12), @MyDate ) - datePart(d,DATEADD( mm, -(((12 + DATEPART(m, @MyDate)) - 4)%12),@MyDate ))+1 ) )

    commented on Feb 16 2012 7:36AM
    Peso
    100 · 2% · 527
  • Why not use a Calendar table instead? Think data and not computations!

    commented on Feb 16 2012 11:39AM
    jcelko
    453 · 0% · 87
  • dishdy: I think you will find that '2012-03-31 23:59:59.999' cannot be stored in the datetime data type, which only has 1/300th ms precision. The only valid digits for the third place of the milliseconds portion are 0, 3, and 7. 999 rounds up to 0.

    jcelko: That's an awful global rule. If you wanted all the invoices from yesterday, would you use data? No? Why the stinking not? I thought you said "data not computations"! Just create a helper table that has all the dates for the next thousand years, and a column that shows "the day before" and join to it. A bargain at half the price!

    Use data where it makes sense, and use computation where it makes sense. That many people get confused with computation is not computation's fault. That you get confused about when to use natural keys (like SSNs even though not everyone has an SSN) is not natural keys' fault.

    commented on Feb 16 2012 12:35PM
    ErikEckhardt
    65 · 3% · 898
  • jcelko: That's an awful global rule. If you wanted all the invoices from yesterday, would you use data? No? <<

    Is that "calendar yesterday" or "prior business day yesterday"? Wow, I have to use a Calendar talbe of some kind!

    Just create a helper table that has all the dates for the next thousand years, and a column that shows "the day before" and join to it.<<

    No, I have found that 100 years (36525 rows) id usually enough. Since the calendars date is the narural key and the data is repetitive, you get huge compression in DB2 and other products. The data is also dense (no gaps), so iou can fill data pages.

    CREATE TABLE Calendar (caldate DATE NOT NULL PRIMARY KEY, julianbusiness_day INTEGER NOT NULL);

    The Julian business day is a good trick. Number the days from whenever your calendar starts and repeat a number for a weekend or company holiday. INSERT INTO Calendar VALUES ('2007-04-05', 42), ('2007-04-06', 43), -- good friday ('2007-04-07', 43), ('2007-04-08', 43), -- Easter sunday ('2007-04-09', 44), ('2007-04-10', 45); --Tuesday

    To compute the business days from Thursday of this week to next Tuesdays:

    SELECT (C2.julianbusinessnbr - C1.julianbusinessnbr) FROM Calendar AS C1, Calendar AS C2 WHERE C1.caldate = '2007-04-05', AND C2caldate = '2007-04-10';

    commented on Feb 16 2012 3:26PM
    jcelko
    453 · 0% · 87
  • @jcelko I totally agree that it is best to use a business calendar table and perform business day calculations on it: what if you declare a special holiday? What if your holiday days change from year to year? What if you start doing business on Saturday, or stop? Trying to encode this in expressions is a fool's errand.

    But I didn't say "prior business day yesterday". I said "yesterday" and by this you were to understand "calendar day". Perhaps invoices was a bad example. If you wanted all the files received yesterday, would you use data? No.

    I'm a great proponent of using tables to record state and protect systems from improper calculations resulting from faulty assumptions. But I don't advocate tables when a simple expression does the job. That's overkill.

    Also, in your example data, wouldn't you want the holidays to have the same julian number as the next business day, not the previous one? If a calculated fulfillment date landed on a holiday, you wouldn't want to quote based on receiving the item earlier than that, unless you possess time travel technology.

    But maybe I've argued myself right into your point... if you're going to calculate first business day of the year, how do you know your business rules will never change? One adjusted long or short year might be called for, and now your entire code base has to be reviewed and substantial sections rewritten, when if you'd used a table with some careful design, you'd be golden. So hats off to the suggestion... just don't take it too far! It isn't a global rule that one should slavishly follow.

    commented on Mar 1 2012 1:38PM
    ErikEckhardt
    65 · 3% · 898
  • I have not had a chance to play with the window clause in aggregates:

    SELECT C1.caldate, COUNT (DISTINCT C1.julianbusinessnbr) OVER (ORDER BY C1.caldate ROWS UNBOUNDED PRECEDING AND CURRENT ROW) FROM Calendar AS C1 WHERE C1.cal_date = '2007-04-05' AND '2007-04-10';

    commented on Mar 1 2012 2:48PM
    jcelko
    453 · 0% · 87
  • Also, in your example data, wouldn't you want the holidays to have the same julian number as the next business day, not the previous one? If a calculated fulfillment date landed on a holiday, you wouldn't want to quote based on receiving the item earlier than that, unless you possess time travel technology.

    Quite literally, "It Depends" on the logic you want to use. I've found that the use of a TOP 1 on the business day number in ascending order as works quite well for me but I can also see doing it the way you suggest.

    commented on Mar 13 2012 11:20PM
    Jeff Moden
    159 · 1% · 304
  • I'm a great proponent of using tables to record state and protect systems from improper calculations resulting from faulty assumptions. But I don't advocate tables when a simple expression does the job. That's overkill.

    And, yes... I definitely agree with THAT! Heh... especially when it comes to READS. ;-)

    commented on Mar 13 2012 11:22PM
    Jeff Moden
    159 · 1% · 304
  • Why not use a Calendar table instead? Think data and not computations!

    I use computations where possible to reduce I/O and improve overall performance.

    For example, if I want the first of the month of a given date, why use a table when I can just do this?:

    SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, date_col, 0)

    commented on Mar 16 2012 9:50AM
    ScottPletcher
    894 · 0% · 32

Your Comment


Sign Up or Login to post a comment.

"Finding out the beginning of current financial year in TSQL" rated 5 out of 5 by 2 readers
Finding out the beginning of current financial year in TSQL , 5.0 out of 5 based on 2 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]