Current year begin and end dates

Question

Current year begin and end dates

alicesql

SSCrazy

Points: 2649
More actions
May 6, 2014 at 7:20 am

#307652

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.

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

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

Ed Wagner SSC Guru Points: 287026 More actions · Answer 1

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

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

alicesql SSCrazy Points: 2649 More actions · Answer 2

alicesql

SSCrazy

Points: 2649

May 6, 2014 at 7:45 am

#1711067

Thanks for your help, and the great link.

andrew gothard SSChampion Points: 12314 More actions · Answer 3

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.

gbritton1 SSCertifiable Points: 6522 More actions · Answer 4

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.

ScottPletcher SSC Guru Points: 101095 More actions · Answer 5

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 6

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)

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 7

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

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 8

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)