Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Current year begin and end dates Expand / Collapse
Author
Message
Posted Tuesday, May 6, 2014 7:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 21, 2014 8:02 AM
Points: 14, Visits: 78
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.
Post #1567932
Posted Tuesday, May 6, 2014 7:27 AM This worked for the OP Answer marked as solution
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, July 18, 2014 2:05 PM
Points: 3,943, Visits: 2,957
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
Post #1567935
Posted Tuesday, May 6, 2014 7:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 21, 2014 8:02 AM
Points: 14, Visits: 78
Thanks for your help, and the great link.
Post #1567952
Posted Tuesday, May 6, 2014 7:45 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 7:04 AM
Points: 325, Visits: 3,194
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.
Post #1567953
Posted Tuesday, May 6, 2014 8:02 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 6:28 AM
Points: 268, Visits: 598
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.
Post #1567967
Posted Tuesday, May 6, 2014 8:27 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 2:20 PM
Points: 1,959, Visits: 2,893
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1567982
Posted Tuesday, May 6, 2014 11:20 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:19 PM
Points: 36,724, Visits: 31,173
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1568108
Posted Tuesday, May 6, 2014 2:57 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 4:09 PM
Points: 3,121, Visits: 11,395
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









Post #1568198
Posted Tuesday, May 6, 2014 4:54 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:19 PM
Points: 36,724, Visits: 31,173
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1568233
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse