March 4, 2013 at 4:56 pm
I'm not so sure why you insist on using codes for literal dates, but I prefer not to.
Where in the world did I suggest using a format code for anything? You're the one that used it. Remember that I'm the one normally against using character based translations of dates! And, no! Please read! The other method did NOT require a format code.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 4, 2013 at 5:07 pm
Jeff Moden (3/4/2013)
@Scott,I'm not so sure why you insist on using codes for literal dates, but I prefer not to.
Where in the world did I suggest using a format code for anything? You're the one that used it. Remember that I'm the one normally against using character based translations of dates! And, no! Please read! The other method did NOT require a format code.
And I've never suggested not to use CONVERT when it's needed.
The only place I've tried to prevent its use completely is for literal dates.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 4, 2013 at 5:20 pm
ScottPletcher (3/4/2013)
Jeff Moden (3/4/2013)
@Scott,I'm not so sure why you insist on using codes for literal dates, but I prefer not to.
Where in the world did I suggest using a format code for anything? You're the one that used it. Remember that I'm the one normally against using character based translations of dates! And, no! Please read! The other method did NOT require a format code.
And I've never suggested not to use CONVERT when it's needed.
The only place I've tried to prevent its use completely is for literal dates.
True enough but you did say not to use knowledge that most developers might not have because it might affect development time, etc, etc. You've used silent truncation and a format code that someone would have to look up. How is that any better than using a precalculated number of months for something?
Oddly enough, I'd use the same method you used because it's fasterer than the obvious method.
Heh... give it up, Scott. You do things for speed just like I do and you shouldn't hammer on me for doing it... especially when I just happen to show you a way faster than yours. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
March 4, 2013 at 5:49 pm
I for one offer you kudos for your affinity for speed Jeff.
I formally confess that I tried for awhile yesterday to come up with something faster to no avail. Hence I remained a(n) (impressed) lurker.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 4, 2013 at 7:22 pm
I'd love to take the credit for it but it's not my code. It's Scotts (although I'd have done it the same way).
I was just curious as to why Scott used some arcane knowledge when he badmouthed the use of such knowledge by me on a previous thread.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 5, 2013 at 7:25 am
Jeff Moden (3/4/2013)
I'd love to take the credit for it but it's not my code. It's Scotts (although I'd have done it the same way).I was just curious as to why Scott used some arcane knowledge when he badmouthed the use of such knowledge by me on a previous thread.
CONVERT is not arcane. DATEADD is not arcane. Anyone with any experience with SQL will have used those.
22800 is arcane.
It's patently silly to try to equate all of those.
I didn't "badmouth" it just for the sake of it: as I clearly stated, I believed the extremely slight performance gain was outweighed by the loss of understanding for the next developer(s) to use the code, unless one knew or could reasonably expect that the number of rows to be processed would be extremely large.
Comments are fine, as far as they go, but since comments and code can get out of sync with each other, ultimately you must go by the code, since you can never place 100% faith in comments.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 5, 2013 at 7:28 am
ScottPletcher (3/5/2013)
Jeff Moden (3/4/2013)
I'd love to take the credit for it but it's not my code. It's Scotts (although I'd have done it the same way).I was just curious as to why Scott used some arcane knowledge when he badmouthed the use of such knowledge by me on a previous thread.
CONVERT is not arcane. DATEADD is not arcane. Anyone with any experience with SQL will have used those.
22800 is arcane.
It's patently silly to try to equate all of those.
I didn't "badmouth" it just for the sake of it: as I clearly stated, I believed the extremely slight performance gain was outweighed by the loss of understanding for the next developer(s) to use the code, unless one knew or could reasonably expect that the number of rows to be processed would be extremely large.
Comments are fine, as far as they go, but since comments and code can get out of sync with each other, ultimately you must go by the code, since you can never place 100% faith in comments.
Silent truncation is arcane knowledge. Most developers coming to SQL believe it to be an ERROR.
As for comments getting out of sync, only if the developer allows it and that makes it their responsibility to maintain as well the code. If you think about it it may be MORE important than the actual code.
Time to give this "debate" a break and move on to something more worthwhile.
March 5, 2013 at 7:39 am
Lynn Pettis (3/5/2013)
ScottPletcher (3/5/2013)
Jeff Moden (3/4/2013)
I'd love to take the credit for it but it's not my code. It's Scotts (although I'd have done it the same way).I was just curious as to why Scott used some arcane knowledge when he badmouthed the use of such knowledge by me on a previous thread.
CONVERT is not arcane. DATEADD is not arcane. Anyone with any experience with SQL will have used those.
22800 is arcane.
It's patently silly to try to equate all of those.
I didn't "badmouth" it just for the sake of it: as I clearly stated, I believed the extremely slight performance gain was outweighed by the loss of understanding for the next developer(s) to use the code, unless one knew or could reasonably expect that the number of rows to be processed would be extremely large.
Comments are fine, as far as they go, but since comments and code can get out of sync with each other, ultimately you must go by the code, since you can never place 100% faith in comments.
Silent truncation is arcane knowledge. Most developers coming to SQL believe it to be an ERROR.
As for comments getting out of sync, only if the developer allows it and that makes it their responsibility to maintain as well the code. If you think about it it may be MORE important than the actual code.
Time to give this "debate" a break and move on to something more worthwhile.
Not all developers are perfect; in fact, not any developer is perfect. Sometimes comments do not get copied with the applicable code. And there is no way you can rely on comments outside of the code: the code is what runs, not any comments.
I also stated that (12 * 1900) was acceptable to me -- it's essentially self-commenting. The best of both worlds to me. But he stated that would still hurt performance too much.
Perhaps you can move on now.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 5, 2013 at 7:43 am
ScottPletcher (3/5/2013)
Lynn Pettis (3/5/2013)
ScottPletcher (3/5/2013)
Jeff Moden (3/4/2013)
I'd love to take the credit for it but it's not my code. It's Scotts (although I'd have done it the same way).I was just curious as to why Scott used some arcane knowledge when he badmouthed the use of such knowledge by me on a previous thread.
CONVERT is not arcane. DATEADD is not arcane. Anyone with any experience with SQL will have used those.
22800 is arcane.
It's patently silly to try to equate all of those.
I didn't "badmouth" it just for the sake of it: as I clearly stated, I believed the extremely slight performance gain was outweighed by the loss of understanding for the next developer(s) to use the code, unless one knew or could reasonably expect that the number of rows to be processed would be extremely large.
Comments are fine, as far as they go, but since comments and code can get out of sync with each other, ultimately you must go by the code, since you can never place 100% faith in comments.
Silent truncation is arcane knowledge. Most developers coming to SQL believe it to be an ERROR.
As for comments getting out of sync, only if the developer allows it and that makes it their responsibility to maintain as well the code. If you think about it it may be MORE important than the actual code.
Time to give this "debate" a break and move on to something more worthwhile.
Not all developers are perfect; in fact, not any developer is perfect. Sometimes comments do not get copied with the applicable code. And there is no way you can rely on comments outside of the code: the code is what runs, not any comments.
I also stated that (12 * 1900) was acceptable to me -- it's essentially self-commenting. The best of both worlds to me. But he stated that would still hurt performance too much.
Perhaps you can move on now.
You too, or do you really need the last word?
March 5, 2013 at 8:00 am
Lynn Pettis (3/5/2013)
ScottPletcher (3/5/2013)
Lynn Pettis (3/5/2013)
ScottPletcher (3/5/2013)
Jeff Moden (3/4/2013)
I'd love to take the credit for it but it's not my code. It's Scotts (although I'd have done it the same way).I was just curious as to why Scott used some arcane knowledge when he badmouthed the use of such knowledge by me on a previous thread.
CONVERT is not arcane. DATEADD is not arcane. Anyone with any experience with SQL will have used those.
22800 is arcane.
It's patently silly to try to equate all of those.
I didn't "badmouth" it just for the sake of it: as I clearly stated, I believed the extremely slight performance gain was outweighed by the loss of understanding for the next developer(s) to use the code, unless one knew or could reasonably expect that the number of rows to be processed would be extremely large.
Comments are fine, as far as they go, but since comments and code can get out of sync with each other, ultimately you must go by the code, since you can never place 100% faith in comments.
Silent truncation is arcane knowledge. Most developers coming to SQL believe it to be an ERROR.
As for comments getting out of sync, only if the developer allows it and that makes it their responsibility to maintain as well the code. If you think about it it may be MORE important than the actual code.
Time to give this "debate" a break and move on to something more worthwhile.
Not all developers are perfect; in fact, not any developer is perfect. Sometimes comments do not get copied with the applicable code. And there is no way you can rely on comments outside of the code: the code is what runs, not any comments.
I also stated that (12 * 1900) was acceptable to me -- it's essentially self-commenting. The best of both worlds to me. But he stated that would still hurt performance too much.
Perhaps you can move on now.
You too, or do you really need the last word?
Which is really the more extreme position:
that 22800 is arcane <or>
that (1900 * 12) is just way too much overhead vs 22800 to be useable??
Wouldn't SQL pre-resolve the literal 1900 * 12 to a constant anyway?!
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 5, 2013 at 8:50 am
ScottPletcher (3/5/2013)
I also stated that (12 * 1900) was acceptable to me -- it's essentially self-commenting. The best of both worlds to me. But he stated that would still hurt performance too much.
I'm not sure I want to get dragged in to this, but technically Jeff said that it needed re-testing as the results of previous testing were quite some time ago -
Jeff Moden (3/1/2013)
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.
I tested the 22800 and (1900 * 12) solutions when the thread kicked off into a debate and found them to be equal.
SET NOCOUNT ON;
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment;
END;
SELECT TOP 1000000 IDENTITY(INT,1,1) AS ID,
(ABS(CHECKSUM(NEWID())) % 5) + 2012 AS ThisYear,
(ABS(CHECKSUM(NEWID())) % 12) + 1 AS ThisMonth
INTO #testEnvironment
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3;
DECLARE @Loop CHAR(1) = '0', @HOLDER DATETIME, @Duration CHAR(12), @StartTime DATETIME;
WHILE @Loop <= 5
BEGIN
RAISERROR('Loop: %s',0,1,@Loop) WITH NOWAIT;
RAISERROR('============',0,1) WITH NOWAIT;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SELECT @StartTime = GETDATE();
SELECT @HOLDER = DATEADD(mm, ThisYear * 12 - 22801 + ThisMonth, 0)
FROM #testEnvironment;
SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);
RAISERROR('22800 Duration: %s',0,1,@Duration) WITH NOWAIT;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SELECT @StartTime = GETDATE();
SELECT @HOLDER = DATEADD(mm, (ThisYear - 1900) * 12 + ThisMonth - 1, 0)
FROM #testEnvironment;
SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);
RAISERROR('(1900 * 12) Duration: %s',0,1,@Duration) WITH NOWAIT;
SET @Loop = @Loop + 1;
END
Loop: 0
============
22800 Duration: 00:00:00:410
(1900 * 12) Duration: 00:00:00:410
Loop: 1
============
22800 Duration: 00:00:00:413
(1900 * 12) Duration: 00:00:00:407
Loop: 2
============
22800 Duration: 00:00:00:390
(1900 * 12) Duration: 00:00:00:403
Loop: 3
============
22800 Duration: 00:00:00:380
(1900 * 12) Duration: 00:00:00:380
Loop: 4
============
22800 Duration: 00:00:00:397
(1900 * 12) Duration: 00:00:00:637
Loop: 5
============
22800 Duration: 00:00:00:350
(1900 * 12) Duration: 00:00:00:347
Second run: -
Loop: 0
============
22800 Duration: 00:00:00:640
(1900 * 12) Duration: 00:00:00:377
Loop: 1
============
22800 Duration: 00:00:00:380
(1900 * 12) Duration: 00:00:00:373
Loop: 2
============
22800 Duration: 00:00:00:373
(1900 * 12) Duration: 00:00:00:390
Loop: 3
============
22800 Duration: 00:00:00:377
(1900 * 12) Duration: 00:00:00:380
Loop: 4
============
22800 Duration: 00:00:00:373
(1900 * 12) Duration: 00:00:00:373
Loop: 5
============
22800 Duration: 00:00:00:390
(1900 * 12) Duration: 00:00:00:383
Third run: -
Loop: 0
============
22800 Duration: 00:00:00:397
(1900 * 12) Duration: 00:00:00:420
Loop: 1
============
22800 Duration: 00:00:00:370
(1900 * 12) Duration: 00:00:00:387
Loop: 2
============
22800 Duration: 00:00:00:403
(1900 * 12) Duration: 00:00:00:363
Loop: 3
============
22800 Duration: 00:00:00:367
(1900 * 12) Duration: 00:00:00:397
Loop: 4
============
22800 Duration: 00:00:00:383
(1900 * 12) Duration: 00:00:00:370
Loop: 5
============
22800 Duration: 00:00:00:367
(1900 * 12) Duration: 00:00:00:383
* edited to remove the "tabs" from the sample code I posted above as it screwed with the formatting.
June 12, 2013 at 11:40 am
gmac 41947 (3/1/2013)
Hi,I use
DATEPART(month, myDate)or
MONTH(myDate)the resut is: 2 (if myDate is 2013.02.03). I would like return: 02
Do you have any solution for this?
Thans!
try this
declare @MyDate datetime
set @mydate= '20130301'
select replicate ('0',2-len(month(@mydate)))+
cast(month(@mydate) as varchar(3))
----------------------------------------------------
June 12, 2013 at 2:07 pm
mmartin1 (6/12/2013)
gmac 41947 (3/1/2013)
Hi,I use
DATEPART(month, myDate)or
MONTH(myDate)the resut is: 2 (if myDate is 2013.02.03). I would like return: 02
Do you have any solution for this?
Thans!
try this
declare @MyDate datetime
set @mydate= '20130301'
select replicate ('0',2-len(month(@mydate)))+
cast(month(@mydate) as varchar(3))
This will work:
declare @MyDate datetime
set @mydate= '20130301'
select right('0' + cast(month(@MyDate) as varchar), 2)
June 12, 2013 at 2:41 pm
Lynn Pettis (6/12/2013)
mmartin1 (6/12/2013)
gmac 41947 (3/1/2013)
Hi,I use
DATEPART(month, myDate)or
MONTH(myDate)the resut is: 2 (if myDate is 2013.02.03). I would like return: 02
Do you have any solution for this?
Thans!
try this
declare @MyDate datetime
set @mydate= '20130301'
select replicate ('0',2-len(month(@mydate)))+
cast(month(@mydate) as varchar(3))
This will work:
declare @MyDate datetime
set @mydate= '20130301'
select right('0' + cast(month(@MyDate) as varchar), 2)
This also:
SELECT CONVERT(varchar(2), @MyDate, 1)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply