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


get the first and last day of any Year/Month


get the first and last day of any Year/Month

Author
Message
Eugene Elutin
Eugene Elutin
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12334 Visits: 5478

...
I guess I'll have to retest on that. In multiple different threads, Michael and Peter showed that controlling the order of execution within the formula with parenthesis performed worse but that was a long time ago and could stand a revisit.
...


I was really joking about CPU cycles...
That was done purely for better readability and understanding, as 1900 can be easily recognised as a common SQL Server start year when 22800 looks a bit strange.
Actually, there is another way of having it done:
sometimes, detailed calendar table could be the answer
let say
CALENDAR (Date date, Year int, Month int, Day int, FirstDayOfMonth date, LastDayOfMonth date, ...)
so you can simply query it (eg using CROSS APPLY) as
SELECT FirstDayOfMonth, LastDayOfMonth
FROM CALENDAR
WHERE Year = @Year AND Month = @Month AND Day = 1

For some reason I think that with proper indexes on CALENDAR table the above will give the best performance.

Now, about using strings.
Actually, you will often hear two opinions on this subject:
1. My one
&
2. Wrong one
Hehe

Seriously, some will still use string conversion as it's easier for them to understand really function does. Another ones, with a bit more knowledge about integer math behind of SQL dates, will be more than happy to use one based in calculations.
It's like using logic based on bit math operations. Some people just do not get it. So they do avoid it.;-)

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
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: 19525 Visits: 7410
Jeff Moden (3/1/2013)
Eugene Elutin (2/28/2013)
This cut down number of CPU cycles when performing multiplication as it takes less number of bit-moves for multiplying smaller numbers - I should accept that it's hard to measure with existing technology Hehe (plus it doesn't use some strange number of 22800):


select dateadd(month, (@YearParam - 1900)*12 + @MonthParam- 1, 0) AS BOM
select dateadd(month, (@YearParam - 1900)*12 + @MonthParam, -1) AS EOM




I guess I'll have to retest on that. In multiple different threads, Michael and Peter showed that controlling the order of execution within the formula with parenthesis performed worse but that was a long time ago and could stand a revisit.

None the less, even if it were a tiny bit slower, it's a much better "readability" solution than using character based conversions to do the same thing (which is what my main point was) if someone really wanted to make that small trade off. I typically won't make that trade off. I'll include a comment for clarity, instead.



That code is much better since it's inherently understandable.

Yes, you can comment the "magic number" code, but it would take developers a considerable amount of time to wade thru the comments before they could begin addressing the real code issue that caused them to look at the code in the first place.

With mutliple hundreds of developers where I've been at, they simply won't all be excellent. It just won't happen. So unless data volumes are massive, or genuinely may become massive, or the performance difference is truly significant, more straightforward code works vastly better overall.

The major exception is triggers: triggers are always coded as efficiently as possible, period; use comments as needed to clarify the code. Since trigger coding is limited to only DBAs or, when absolutely necessary, top developers, this is not an issue anyway.

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.
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: 19525 Visits: 7410
Lynn Pettis (3/1/2013)
ben.brugman (3/1/2013)
Jeff Moden (2/26/2013)

Just be aware that string conversions of dates will be a bit slower than integer conversions. It'll take a million rows to notice a difference but every bit helps when you're working with large tables or millions of hits each day.


I use string because that is much faster. If taken the total time I need to implement a query and execute a query for me working with strings is much faster in the end.

If the query has to be executed lot's of times and on large tables sometimes it is worth investing the extra time to come up with a faster solution. (If the table is not in core (sorry memory), then the string handling probably does not matter.)

Using the strings might even make the code more maintainable for others.

But if the code is repeatedly used, other code might be more suetable.

Ben
(Sometimes investing in faster methods and then discovering that the time spend for this is not returned in the actual use;-)).


I'll have to disagree with you and side with Jeff. I have learned much from him and scalability and reuse actually come hand in hand. If you take the time now to build scalable code (read routines) that can be reused then spend the time. Just because it is good enough for a particular use does not mean it will be for another. Many times people will take what you have written and use it where it really shouldn't because it was just good enough for that one instance.



Code reuse is another tricky subject. It generally gets talked about far more often than it is genuinely done. A few developers are bad enough, but trying to get hundreds of developers to build and use reuseable code is like trying to get Congress to agree on something.

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 (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)

Group: General Forum Members
Points: 213859 Visits: 41977
ben.brugman (3/1/2013)
Jeff Moden (2/26/2013)

Just be aware that string conversions of dates will be a bit slower than integer conversions. It'll take a million rows to notice a difference but every bit helps when you're working with large tables or millions of hits each day.


I use string because that is much faster. If taken the total time I need to implement a query and execute a query for me working with strings is much faster in the end.

If the query has to be executed lot's of times and on large tables sometimes it is worth investing the extra time to come up with a faster solution. (If the table is not in core (sorry memory), then the string handling probably does not matter.)

Using the strings might even make the code more maintainable for others.

But if the code is repeatedly used, other code might be more suetable.

Ben
(Sometimes investing in faster methods and then discovering that the time spend for this is not returned in the actual use;-)).


Fortunately, you don't have to discover faster methods because I just handed one to you on a silver platter and you should incorporate it into your code library so you don't have to spend any time at it the next time you need it. ;-) The smart developer does spend some time learning new things to make it so they never have to hear the words "Hey! your code is slow!" from your boss.

--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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)

Group: General Forum Members
Points: 213859 Visits: 41977
ScottPletcher (3/1/2013)
[quote]
Code reuse is another tricky subject. It generally gets talked about far more often than it is genuinely done. A few developers are bad enough, but trying to get hundreds of developers to build and use reuseable code is like trying to get Congress to agree on something.


Companies that have "hundreds of developers" will also have code libraries and standards to follow. Usually, the first standard is to check the library for code simply because eliminates testing because the code is already approved. That saves money and development time.

--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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)

Group: General Forum Members
Points: 213859 Visits: 41977
ScottPletcher (3/1/2013)
Jeff Moden (3/1/2013)
Eugene Elutin (2/28/2013)
This cut down number of CPU cycles when performing multiplication as it takes less number of bit-moves for multiplying smaller numbers - I should accept that it's hard to measure with existing technology Hehe (plus it doesn't use some strange number of 22800):


select dateadd(month, (@YearParam - 1900)*12 + @MonthParam- 1, 0) AS BOM
select dateadd(month, (@YearParam - 1900)*12 + @MonthParam, -1) AS EOM




I guess I'll have to retest on that. In multiple different threads, Michael and Peter showed that controlling the order of execution within the formula with parenthesis performed worse but that was a long time ago and could stand a revisit.

None the less, even if it were a tiny bit slower, it's a much better "readability" solution than using character based conversions to do the same thing (which is what my main point was) if someone really wanted to make that small trade off. I typically won't make that trade off. I'll include a comment for clarity, instead.



That code is much better since it's inherently understandable.

Yes, you can comment the "magic number" code, but it would take developers a considerable amount of time to wade thru the comments before they could begin addressing the real code issue that caused them to look at the code in the first place.

With mutliple hundreds of developers where I've been at, they simply won't all be excellent. It just won't happen. So unless data volumes are massive, or genuinely may become massive, or the performance difference is truly significant, more straightforward code works vastly better overall.

The major exception is triggers: triggers are always coded as efficiently as possible, period; use comments as needed to clarify the code. Since trigger coding is limited to only DBAs or, when absolutely necessary, top developers, this is not an issue anyway.


If what you say is true, then even that simple code will leave the non-excellent developers scratching their head. Comments will be necessary with either.

If everyone wrote code to the lowest common denominator, all code would be dreadfully slow or resource inefficient.

--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
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: 19525 Visits: 7410
Jeff Moden (3/1/2013)
ScottPletcher (3/1/2013)
[quote]
Code reuse is another tricky subject. It generally gets talked about far more often than it is genuinely done. A few developers are bad enough, but trying to get hundreds of developers to build and use reuseable code is like trying to get Congress to agree on something.


Companies that have "hundreds of developers" will also have code libraries and standards to follow. Usually, the first standard is to check the library for code simply because eliminates testing because the code is already approved. That saves money and development time.



Very easy to say, extraordinarly hard to do in actual practice.

You have to determine in an existing needed function, say, already exists. Verify that it does indeed do what you are looking for, without unacceptable side effects, etc..

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.
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: 19525 Visits: 7410
Jeff Moden (3/1/2013)
ScottPletcher (3/1/2013)
Jeff Moden (3/1/2013)
Eugene Elutin (2/28/2013)
This cut down number of CPU cycles when performing multiplication as it takes less number of bit-moves for multiplying smaller numbers - I should accept that it's hard to measure with existing technology Hehe (plus it doesn't use some strange number of 22800):


select dateadd(month, (@YearParam - 1900)*12 + @MonthParam- 1, 0) AS BOM
select dateadd(month, (@YearParam - 1900)*12 + @MonthParam, -1) AS EOM




I guess I'll have to retest on that. In multiple different threads, Michael and Peter showed that controlling the order of execution within the formula with parenthesis performed worse but that was a long time ago and could stand a revisit.

None the less, even if it were a tiny bit slower, it's a much better "readability" solution than using character based conversions to do the same thing (which is what my main point was) if someone really wanted to make that small trade off. I typically won't make that trade off. I'll include a comment for clarity, instead.



That code is much better since it's inherently understandable.

Yes, you can comment the "magic number" code, but it would take developers a considerable amount of time to wade thru the comments before they could begin addressing the real code issue that caused them to look at the code in the first place.

With mutliple hundreds of developers where I've been at, they simply won't all be excellent. It just won't happen. So unless data volumes are massive, or genuinely may become massive, or the performance difference is truly significant, more straightforward code works vastly better overall.

The major exception is triggers: triggers are always coded as efficiently as possible, period; use comments as needed to clarify the code. Since trigger coding is limited to only DBAs or, when absolutely necessary, top developers, this is not an issue anyway.


If what you say is true, then even that simple code will leave the non-excellent developers scratching their head. Comments will be necessary with either.

If everyone wrote code to the lowest common denominator, all code would be dreadfully slow or resource inefficient.



Reductio ad absurdum. No one's advocating anything close to that.

Highly obscure coding practices to gain a few milliseconds per 100K+ rows, when realistically only 5,000 rows will ever be processed, just causes maintenance issues.

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.
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94289 Visits: 38955
ScottPletcher (3/1/2013)
Jeff Moden (3/1/2013)
ScottPletcher (3/1/2013)
[quote]
Code reuse is another tricky subject. It generally gets talked about far more often than it is genuinely done. A few developers are bad enough, but trying to get hundreds of developers to build and use reuseable code is like trying to get Congress to agree on something.


Companies that have "hundreds of developers" will also have code libraries and standards to follow. Usually, the first standard is to check the library for code simply because eliminates testing because the code is already approved. That saves money and development time.



Very easy to say, extraordinarly hard to do in actual practice.

You have to determine in an existing needed function, say, already exists. Verify that it does indeed do what you are looking for, without unacceptable side effects, etc..


So, instead of doing all that, just keep reinventing the wheel, right?

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
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: 19525 Visits: 7410
Jeff Moden (3/1/2013)
ScottPletcher (3/1/2013)
Jeff Moden (3/1/2013)
Eugene Elutin (2/28/2013)
This cut down number of CPU cycles when performing multiplication as it takes less number of bit-moves for multiplying smaller numbers - I should accept that it's hard to measure with existing technology Hehe (plus it doesn't use some strange number of 22800):


select dateadd(month, (@YearParam - 1900)*12 + @MonthParam- 1, 0) AS BOM
select dateadd(month, (@YearParam - 1900)*12 + @MonthParam, -1) AS EOM




I guess I'll have to retest on that. In multiple different threads, Michael and Peter showed that controlling the order of execution within the formula with parenthesis performed worse but that was a long time ago and could stand a revisit.

None the less, even if it were a tiny bit slower, it's a much better "readability" solution than using character based conversions to do the same thing (which is what my main point was) if someone really wanted to make that small trade off. I typically won't make that trade off. I'll include a comment for clarity, instead.



That code is much better since it's inherently understandable.

Yes, you can comment the "magic number" code, but it would take developers a considerable amount of time to wade thru the comments before they could begin addressing the real code issue that caused them to look at the code in the first place.

With mutliple hundreds of developers where I've been at, they simply won't all be excellent. It just won't happen. So unless data volumes are massive, or genuinely may become massive, or the performance difference is truly significant, more straightforward code works vastly better overall.

The major exception is triggers: triggers are always coded as efficiently as possible, period; use comments as needed to clarify the code. Since trigger coding is limited to only DBAs or, when absolutely necessary, top developers, this is not an issue anyway.


If what you say is true, then even that simple code will leave the non-excellent developers scratching their head. Comments will be necessary with either.



C'mon, be real, that code is clear enough that a decent developer will understand what it means. Anyone who's developed for SQL for any period of time understands that SQL's base date is 1900.

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