Implementing Date Calculations in SQL

  • Comments posted to this topic are about the item Implementing Date Calculations in SQL

    Aveek has been working as an Analytics Engineer for the past couple of years now. His main areas of interests is SQL Server, SSIS, and SSAS. On his leisure, he likes to travel and is also an amateur photographer.
    https://twitter.com/avikoleum
    https://www.linkedin.com/in/aveekd22/

  • Wondering why you are not using the EOMONTH function for the last day of the relevant months?

  • Rather than having to refresh your dates every day, wouldn't it have been simpler to implement a view with dynamic dates?

    Each refresh of the report is only ever pulling 2 dates from the view so it's not like its a performance bottleneck.

  • graham.wade 69740 wrote:

    Wondering why you are not using the EOMONTH function for the last day of the relevant months?

    For what was written as code in the article, I'm wondering the same thing.  Unfortunately, the code in the article expresses the wrong method to do such date calculations because the WHERE clause in the reporting code uses BETWEEN.  It means that, if someone writes similar code against a DATETIME2(7) column, the last 3 milliseconds of the day will be missed and a whole lot can happen in those 3 milliseconds that can cause substantial transactions to be missed throwing totals off by quite a bit.  Both EOMONTH and the use of BETWEEN, IMHO, should be outlawed as a "Worst Practice".

    The "Best Practice" would be to have used the standard "closed/open" temporal boundaries in the following form...

    WHERE SomeDateTimeColumn >= CURRENTPeriodStart AND SomeDateColumn < NEXTPeriodEnd

    ... where the period start and ends are "whole" dates with a "midnight time".

    The code that wasn't changed in the example was the code that most desperately needed to change.

    I'm also dead set against having to maintain such a lookup table because you can't control when someone or something is going to run a report and the table may have been updated too early or too late and, again, milliseconds DO matter in this area.  The view (actually, I'd make it an iTVF so you could vary the "today" date if needed... that won't work in a view) that Norty303 suggested would be more timely and require no maintenance.aya

    And, no... the common justification for using BETWEEN because, supposedly, only whole dates will be used in the tables doesn't hold water because you simply cannot control what may happen to that table in the future.  Someone may suddenly decide that the datatype and the data needs to change to something more finite.   You also can't control what some desperate programmer may copy your code for.  Use the standard that I cited above ALL the time and your code will be bullet-proof in those areas.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • @aveek22 ,

    Man, once again, very well written article.  I just have to seriously disagree with the methods you used.  Please see my previous post above for why.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Hello,

    Going forward this post, I'd like to ask about my issue, that touching date and time subject.

    We use a Retail Calendar (445) which is different from the regular one. The year begins in October and the first day of every month is not the real first day of this month. For example for this fiscal year (2020), November begins on Oct. 6th and ends on Nov. 2d, next year there will be a different dates.

    I need to create an offset field/s calculating the differences between two points of times based on that calendar.

    The calculation should be dynamic and comparing every time between the date of today and the second (past/ future/ present date).

    I used many kinds of script, but no one of them works with that kind of calendar.

    I have my "445 calendar" and know when every month begins and ending as well as the year, so I don't need to calculate the calendar itself.. 445 means the months will have 4,4 and 5 weeks in them, for example, October will contain 4 weeks, November - 4 weeks, December 5 weeks, Jan.- 4, Feb -4, March -5, etc. My problem is to calculate this Offset. I did it successful for the regular calendar.

     

    Thank you!

  • I posted a function that calculates the 4-5-4, 5-4-4 and 4-4-5 calendars for a 3 year period here: https://www.sqlservercentral.com/scripts/fiscal-retail-4-5-4-calendar-function

    If I am understanding your request - you need to figure out the 4-4-5 portion of the calendar.  If that is the case - the function does that using the formula: ((WeekInQuarter - 1) / 4) - (WeekInQuarter / 13) + 1

    The caveat is what to do with the 53rd week of a year.  In the 4-4-5 calendar the last quarter would end up as 4-4-6 - or you restate the calendar where you shift the weeks in the year back one so that year starts one week later and there are only 52 weeks.  In most cases, the 4-4-5 calendar would be reset to a 4-5-5 for comparison in the non-restated version.

    If that isn't the issue you are having - can you explain it further?

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Another nice aspect of this approach would be if you made the stored procedure use a Temp Table and a Merge, while making the TimePeriods table a temporal table.  This would also allow you to pull older versions of the query more easily using the temporal syntax to gather your dates.

    Also, I would agree that using the following would be a more accurate approach.

    WHERE [SalesDate] >= @PeriodStartDateTime

    AND [SalesDate] < @PeriodEndDateTime

     

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply