New Year, Date question

[datch]

Hey guys, first time post here so, y'know... be gentle. And I apologize in advance if the answer to this question can be found elsewhere in the forums.

 

So! I'm looking through some report code and found the following:

 

BETWEEN DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) AND ((DATEADD(wk, DATEDIFF(wk, 5, GETDATE()-7), 5))+1)

 

 

So, first thing, I'm aware of the unnecessary aspects of the code (that +1 could just be incorporated by changing the DATEDIFF 5 into a 6)

 

Second thing is that currently this creates the parameters BETWEEN (Jan 1, 2011) and (current date, roughly)

 

The question I have is this: when the new year rolls around, will this create parameters (Jan 1, 2011) and (current date, roughly) or will it move ahead and get parameters (Jan 1, 2012) and (current date, roughly)?

 

 

Thanks so very much, guys. And, if you could also post the rationale behind the answer in addition to the answer, that'd be great. I'd like to understand the truth in addition to knowing it!

 

 

A thousand thanks.

 

-Chris

 

P.S.

(the following is the code in its entirety, in case for some reason it's necessary: I can't even fathom why it would be, but, you never know what you never know):

 

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

SELECT "Category", "AppealCode", ISNULL("EPAppeals", 0) AS "EPAppealCount", ISNULL("EHAppeals", 0) AS "EHAppealCount"

FROM

(SELECT DISTINCT r_appeal_category.tx_appeal_category AS "Category", r_appeal_category.cd_appeal_category AS "AppealCode"

FROM r_appeal_category

WHERE

r_appeal_category.fl_active = '1'

) TBL1

 

LEFT OUTER JOIN

(SELECT r_appeal_category.tx_appeal_category AS "Category2", COUNT(*) AS "EPAppeals"

FROM r_appeal_category

INNER JOIN r_appeal_type

ON r_appeal_category.cd_appeal_category = r_appeal_type.cd_appeal_category

INNER JOIN e_appeal

ON e_appeal.cd_appeal_type = r_appeal_type.cd_appeal_type

INNER JOIN e_program_year_enrollment

ON e_appeal.id_provider = e_program_year_enrollment.id_provider

AND e_appeal.cd_incentive_yr = e_program_year_enrollment.cd_incentive_yr

WHERE e_program_year_enrollment.nm_entity_type = 'EP'

AND r_appeal_category.fl_active = '1'

AND e_appeal.ts_cr BETWEEN DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) AND ((DATEADD(wk, DATEDIFF(wk, 5, GETDATE()-7), 5))+1)

GROUP BY r_appeal_category.tx_appeal_category

) TBL2

ON "Category" = "Category2"

 

LEFT OUTER JOIN

(SELECT r_appeal_category.tx_appeal_category AS "Category3", COUNT(*) AS "EHAppeals"

FROM r_appeal_category

INNER JOIN r_appeal_type

ON r_appeal_category.cd_appeal_category = r_appeal_type.cd_appeal_category

INNER JOIN e_appeal

ON e_appeal.cd_appeal_type = r_appeal_type.cd_appeal_type

INNER JOIN e_program_year_enrollment

ON e_appeal.id_provider = e_program_year_enrollment.id_provider

AND e_appeal.cd_incentive_yr = e_program_year_enrollment.cd_incentive_yr

WHERE e_program_year_enrollment.nm_entity_type = 'EH'

AND r_appeal_category.fl_active = '1'

AND e_appeal.ts_cr BETWEEN DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) AND ((DATEADD(wk, DATEDIFF(wk, 5, GETDATE()-7), 5))+1)

GROUP BY r_appeal_category.tx_appeal_category

) TBL3

ON "Category" = "Category3"

ORDER BY "AppealCode"

[gizmola]

The answer is: yes it will continue to work.  I think you can figure this out by looking at the functions being used:

 

 

http://msdn.microsoft.com/en-us/library/ms186819.aspx

http://msdn.microsoft.com/en-us/library/ms188383.aspx

http://msdn.microsoft.com/en-us/library/ms189794.aspx

 

You can run these functions interactively and answer your own questions as to whether or not you would agree.

 

For example, when you take a piece of the first function that generates the start date for the between....

 

SELECT GETDATE();
SELECT DATEDIFF(yy,0,getdate());
SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0);

 

It should be pretty apparent that this is going to generate the first day of the current calendar year.  Since this is driven by GETDATE(), and a specific year is not hardwired, it should be easy to see that will continue to work.  The 2nd part of the between does not look to me like its purpose is to generate the "Current date".  It is attempting to work with "weeks" and there appears to be some code missing, so it's a bit hard to tell exactly.  Again the best way to work this out is to go into a command line client and try out the individual pieces so you understand what they are returning.