SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Current year begin and end dates


Current year begin and end dates

Author
Message
alicesql
alicesql
SSC Eights!
SSC Eights! (860 reputation)SSC Eights! (860 reputation)SSC Eights! (860 reputation)SSC Eights! (860 reputation)SSC Eights! (860 reputation)SSC Eights! (860 reputation)SSC Eights! (860 reputation)SSC Eights! (860 reputation)

Group: General Forum Members
Points: 860 Visits: 1115
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.
Ed Wagner
Ed Wagner
SSC-Forever
SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)

Group: General Forum Members
Points: 49523 Visits: 10844
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
alicesql
SSC Eights!
SSC Eights! (860 reputation)SSC Eights! (860 reputation)SSC Eights! (860 reputation)SSC Eights! (860 reputation)SSC Eights! (860 reputation)SSC Eights! (860 reputation)SSC Eights! (860 reputation)SSC Eights! (860 reputation)

Group: General Forum Members
Points: 860 Visits: 1115
Thanks for your help, and the great link.
andrew gothard
andrew gothard
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2790 Visits: 5969
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
gbritton1
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1427 Visits: 879
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
ScottPletcher
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19813 Visits: 7412
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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)

Group: General Forum Members
Points: 217070 Visits: 41991
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Michael Valentine Jones
Michael Valentine Jones
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14649 Visits: 11848
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
Jeff Moden
SSC Guru
SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)

Group: General Forum Members
Points: 217070 Visits: 41991
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search