Current year begin and end dates

  • I am working on some payroll related code which currently has the following hard-coded CASE statement (I won't include the entire thing, it is lengthy):

    AND b.TCDateTime BETWEEN '2013-01-01 0:00' and '2013-12-31 23:59'

    I want to get rid of the hard coding, and have this use current year dates. So for 2014, it should reference rows where the TCDateTime is >='2014-01-01 0:00' AND <'2015-01-01 0:00'

    I think the following would accomplish this, but would like confirmation that this is the 'best' way (and that it will work!)

    SELECT CONVERT(DATETIME, CONVERT(CHAR(4), DATEPART(yyyy, GETDATE())) + '0101') AS curryrbegin

    --output should be yyyy-01-01 00:00:00.0 where yyyy is current year

    SELECT CONVERT(DATETIME, CONVERT(CHAR(4), DATEPART(yyyy + 1, GETDATE()))

    + '0101') AS nextyrbegin

    --output s/b nextyear-01-01-00:00:00.0

    Then, change the CASE statement to read:

    AND (b.TCDateTime >= curryrbegin AND < nextyrbegin)

    Thanks.

  • Lynn Pettis has a post with some very useful date functions that I think you'll like at http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/.

    Specifically, I think you'll like these two:

    select dateadd(yy, datediff(yy, 0, GETDATE()), 0) -- Beginning of this year

    select dateadd(yy, datediff(yy, 0, GETDATE()) + 1, 0) -- Beginning of next year

  • Thanks for your help, and the great link.

  • Personally for things like this, I prefer a DateDim / Calendar table - apart from the fact this makes the SQL CLeaner, and you only have to do the calcs once then look them up - it's also a lot cleaner when things like Financial Year End changes to just run an update against the table than change all the code

    I'm a DBA.
    I'm not paid to solve problems. I'm paid to prevent them.

  • andrew gothard (5/6/2014)


    Personally for things like this, I prefer a DateDim / Calendar table - apart from the fact this makes the SQL CLeaner, and you only have to do the calcs once then look them up - it's also a lot cleaner when things like Financial Year End changes to just run an update against the table than change all the code

    Totally agree. If folks would migrate to this model, the questions and problems around date processing would largely disappear. Also, its cheap to do. Only a few thousand rows (for some years back and some in the future). Capture business rules (fiscal months, weeks and quarters and fiscal year end). Common text representations of dates, days and months. flags for statutory holidays, company holidays, weekends etc.

    Best of all...one source for the rules and conversions ==> only one thing to maintain. I've seen too many fancy date calculations that give wrong results in corner (or even the general!) cases. Using a Date/Calendar table eliminates 99% of the calculations and all of the bugs the calculations cause.

    Really I don't know why this isn't the go-to solution for every business.

  • No reason for the overhead of reading a table just for that. Or to calculate, say, the first Tuesday or last Friday, etc., of a month. Calendar tables just create issues there where none should exist.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • ScottPletcher (5/6/2014)


    No reason for the overhead of reading a table just for that. Or to calculate, say, the first Tuesday or last Friday, etc., of a month. Calendar tables just create issues there where none should exist.

    I tend to agree except for certain esoteric things. Do you have code that you'd like to share for the two "problems" you cited above?

    --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.


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

  • Jeff Moden (5/6/2014)


    ScottPletcher (5/6/2014)


    No reason for the overhead of reading a table just for that. Or to calculate, say, the first Tuesday or last Friday, etc., of a month. Calendar tables just create issues there where none should exist.

    I tend to agree except for certain esoteric things. Do you have code that you'd like to share for the two "problems" you cited above?

    I'll share:

    select

    MonthDaySeven,

    FirstSun = dateadd(dd,(datediff(dd,-53684,a.MonthDaySeven)/7)*7,-53684),

    FirstMon = dateadd(dd,(datediff(dd,-53690,a.MonthDaySeven)/7)*7,-53690),

    FirstTue = dateadd(dd,(datediff(dd,-53689,a.MonthDaySeven)/7)*7,-53689),

    FirstWed = dateadd(dd,(datediff(dd,-53688,a.MonthDaySeven)/7)*7,-53688),

    FirstThu = dateadd(dd,(datediff(dd,-53687,a.MonthDaySeven)/7)*7,-53687),

    FirstFri = dateadd(dd,(datediff(dd,-53686,a.MonthDaySeven)/7)*7,-53686),

    FirstSat = dateadd(dd,(datediff(dd,-53685,a.MonthDaySeven)/7)*7,-53685)

    from

    (select MonthDaySeven=dateadd(dd,6,dateadd(mm,datediff(mm,0,getdate()),0))) a

    select

    MonthLastDay,

    LastSun = dateadd(dd,(datediff(dd,-53684,a.MonthLastDay)/7)*7,-53684),

    LastMon = dateadd(dd,(datediff(dd,-53690,a.MonthLastDay)/7)*7,-53690),

    LastTue = dateadd(dd,(datediff(dd,-53689,a.MonthLastDay)/7)*7,-53689),

    LastWed = dateadd(dd,(datediff(dd,-53688,a.MonthLastDay)/7)*7,-53688),

    LastThu = dateadd(dd,(datediff(dd,-53687,a.MonthLastDay)/7)*7,-53687),

    LastFri = dateadd(dd,(datediff(dd,-53686,a.MonthLastDay)/7)*7,-53686),

    LastSat = dateadd(dd,(datediff(dd,-53685,a.MonthLastDay)/7)*7,-53685)

    from

    ( select MonthLastDay = dateadd(mm,datediff(mm,-1,getdate()),-1) ) a

    Results:

    MonthDaySeven FirstSun FirstMon FirstTue FirstWed FirstThu FirstFri FirstSat

    ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- -----------------------

    2014-05-07 00:00:00.000 2014-05-04 00:00:00.000 2014-05-05 00:00:00.000 2014-05-06 00:00:00.000 2014-05-07 00:00:00.000 2014-05-01 00:00:00.000 2014-05-02 00:00:00.000 2014-05-03 00:00:00.000

    MonthLastDay LastSun LastMon LastTue LastWed LastThu LastFri LastSat

    ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- -----------------------

    2014-05-31 00:00:00.000 2014-05-25 00:00:00.000 2014-05-26 00:00:00.000 2014-05-27 00:00:00.000 2014-05-28 00:00:00.000 2014-05-29 00:00:00.000 2014-05-30 00:00:00.000 2014-05-31 00:00:00.000

    Or, you can encapsulate the start of week logic in a function:

    Start of Week Function:

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307

  • Michael Valentine Jones (5/6/2014)


    Jeff Moden (5/6/2014)


    ScottPletcher (5/6/2014)


    No reason for the overhead of reading a table just for that. Or to calculate, say, the first Tuesday or last Friday, etc., of a month. Calendar tables just create issues there where none should exist.

    I tend to agree except for certain esoteric things. Do you have code that you'd like to share for the two "problems" you cited above?

    I'll share:

    select

    MonthDaySeven,

    FirstSun = dateadd(dd,(datediff(dd,-53684,a.MonthDaySeven)/7)*7,-53684),

    FirstMon = dateadd(dd,(datediff(dd,-53690,a.MonthDaySeven)/7)*7,-53690),

    FirstTue = dateadd(dd,(datediff(dd,-53689,a.MonthDaySeven)/7)*7,-53689),

    FirstWed = dateadd(dd,(datediff(dd,-53688,a.MonthDaySeven)/7)*7,-53688),

    FirstThu = dateadd(dd,(datediff(dd,-53687,a.MonthDaySeven)/7)*7,-53687),

    FirstFri = dateadd(dd,(datediff(dd,-53686,a.MonthDaySeven)/7)*7,-53686),

    FirstSat = dateadd(dd,(datediff(dd,-53685,a.MonthDaySeven)/7)*7,-53685)

    from

    (select MonthDaySeven=dateadd(dd,6,dateadd(mm,datediff(mm,0,getdate()),0))) a

    select

    MonthLastDay,

    LastSun = dateadd(dd,(datediff(dd,-53684,a.MonthLastDay)/7)*7,-53684),

    LastMon = dateadd(dd,(datediff(dd,-53690,a.MonthLastDay)/7)*7,-53690),

    LastTue = dateadd(dd,(datediff(dd,-53689,a.MonthLastDay)/7)*7,-53689),

    LastWed = dateadd(dd,(datediff(dd,-53688,a.MonthLastDay)/7)*7,-53688),

    LastThu = dateadd(dd,(datediff(dd,-53687,a.MonthLastDay)/7)*7,-53687),

    LastFri = dateadd(dd,(datediff(dd,-53686,a.MonthLastDay)/7)*7,-53686),

    LastSat = dateadd(dd,(datediff(dd,-53685,a.MonthLastDay)/7)*7,-53685)

    from

    ( select MonthLastDay = dateadd(mm,datediff(mm,-1,getdate()),-1) ) a

    Results:

    MonthDaySeven FirstSun FirstMon FirstTue FirstWed FirstThu FirstFri FirstSat

    ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- -----------------------

    2014-05-07 00:00:00.000 2014-05-04 00:00:00.000 2014-05-05 00:00:00.000 2014-05-06 00:00:00.000 2014-05-07 00:00:00.000 2014-05-01 00:00:00.000 2014-05-02 00:00:00.000 2014-05-03 00:00:00.000

    MonthLastDay LastSun LastMon LastTue LastWed LastThu LastFri LastSat

    ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- -----------------------

    2014-05-31 00:00:00.000 2014-05-25 00:00:00.000 2014-05-26 00:00:00.000 2014-05-27 00:00:00.000 2014-05-28 00:00:00.000 2014-05-29 00:00:00.000 2014-05-30 00:00:00.000 2014-05-31 00:00:00.000

    Or, you can encapsulate the start of week logic in a function:

    Start of Week Function:

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307

    I love it! We even have a short one for ISO-Week for those still dealing with SQL Server 2005 or less.

    http://www.sqlservercentral.com/articles/T-SQL/97910/

    BTW, the negative numbers that Michael is using are "integer date serial numbers" for dates from the beginning of the DATETIME calendar. Just like "0" can be used for and is sometime faster than using a literal of '1900' or '1900-01-01', so it is with the negative numbers he used. The "integer serial number" for '1753-01-01' can be found in many different ways. Here's one...

    SELECT CAST(DATEADD(dd,0,'1753-01-01') AS INT)

    ... and that produces the "integer date serial number" of -53690.

    Thanks again, Michael.

    --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.


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

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

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