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 «««1234»»

get the first and last day of any Year/Month Expand / Collapse
Author
Message
Posted Friday, March 1, 2013 9:14 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 7:27 AM
Points: 2,836, Visits: 5,067

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


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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1425574
Posted Friday, March 1, 2013 10:40 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:25 PM
Points: 2,044, Visits: 3,059
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 (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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1425633
Posted Friday, March 1, 2013 10:42 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:25 PM
Points: 2,044, Visits: 3,059
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1425635
Posted Friday, March 1, 2013 2:04 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 36,995, Visits: 31,517
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."

(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 #1425736
Posted Friday, March 1, 2013 2:08 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 36,995, Visits: 31,517
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."

(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 #1425739
Posted Friday, March 1, 2013 2:12 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 36,995, Visits: 31,517
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 (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."

(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 #1425742
Posted Friday, March 1, 2013 2:18 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:25 PM
Points: 2,044, Visits: 3,059
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1425746
Posted Friday, March 1, 2013 2:20 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:25 PM
Points: 2,044, Visits: 3,059
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 (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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1425749
Posted Friday, March 1, 2013 2:21 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 4:51 PM
Points: 23,299, Visits: 32,046
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?



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)
Post #1425750
Posted Friday, March 1, 2013 2:23 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:25 PM
Points: 2,044, Visits: 3,059
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 (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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1425751
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse