February 21, 2013 at 8:07 am
Small correction to Scott's code, he has the STUFF for 4 characters, this will wipe out both slashes. You could use either one below to get 'MM/YY':
declare @enddate datetime
set @enddate = getdate()
SELECT STUFF(CONVERT(varchar(10), DATEADD(DAY, -21, @enddate), 1), 3, 3, '')
SELECT STUFF(CONVERT(varchar(10), DATEADD(DAY, -21, @enddate), 1), 4, 3, '')
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
February 21, 2013 at 8:30 pm
Jeff Moden (2/18/2013)
Kingston Dhasian (2/18/2013)
Jeff Moden (2/15/2013)
I suppose consistency is a good reason. But, let's try something just for fun. Write some code to add 41:41:41.041 to a given date.My version
DECLARE@date DATETIMESET@date = CURRENT_TIMESTAMP
SELECT @date AS Date, DATEADD(MILLISECOND, 41, DATEADD(SECOND, 41, DATEADD(MINUTE, 41, DATEADD(HOUR, 41, @date)))) AS Date_Added
I get the output as below
Date Date_Added2013-02-18 10:29:50.343 2013-02-20 04:11:31.383
The only strange thing I observed is that it always 40 milliseconds instead of 41 milliseconds.
Is there any other strange thing?
I'll try to get back to this after worrk tonight. In the mean time, remember that DATETIME has an accuracty of only 3.3 milliseconds. All DATETIMES will end with 0, 3, or 7 for the final digit in the milliseconds.
My apologies, Kingston. My requirements weren't clear. The 41:41:41.041 was supposed to be a VARCHAR.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 21, 2013 at 10:30 pm
dwain.c (2/20/2013)
How about this?
DECLARE @TimeAdd VARCHAR(20) = '41:41:41.041'
SELECT CAST(GETDATE() AS DATETIME2)
,DATEADD(millisecond, 1, DATEADD(hour, CAST(LEFT(@TimeAdd, 2) AS INT)
,DATEADD(millisecond
,DATEDIFF(millisecond, 0, CAST('00:'+RIGHT(@TimeAdd, 9) AS TIME))
,CAST(GETDATE() AS DATETIME2))))
So Jeff, now that I've had my fun, how about showing us how you would do it?
That works fine for DATETIME2 but my intent was to show how using simple date addition can make the code, well... simple. Not only does it make the code much more simple, but there's a performance advantage, as well, and I wish they had included date addition in the newer date related data types.
Of course, without a typical million row test, claims of performance just hearsay. So, without further ado, here's my standard million row test table for such tests...
/**********************************************************************************************************************
Purpose:
Create a voluminous test table with various types of highly randomized data.
--Jeff Moden
**********************************************************************************************************************/
--===== Conditionally drop the test table to make reruns easier
IF OBJECT_ID('dbo.JBMTest','U') IS NOT NULL
DROP TABLE dbo.JBMTest
;
--===== Create and populate a 1,000,000 row test table.
-- "SomeID" has a range of 1 to 1,000,000 unique numbers
-- "SomeInt" has a range of 1 to 50,000 non-unique numbers
-- "SomeLetters2" has a range of "AA" to "ZZ" non-unique 2 character strings
-- "SomeMoney has a range of 0.00 to 100.00 non-unique numbers
-- "SomeDateTime" has a range of >=01/01/2000 and <01/01/2020 non-unique date/times
-- "SomeDate" has a range of >=01/01/2000 and <01/01/2020 non-unique "whole dates"
-- "SomeName" contains random characters at random lengths from 2 to 20 characters
SELECT TOP 1000000
SomeID = IDENTITY(INT,1,1),
SomeInt = ABS(CHECKSUM(NEWID())) % 50000 + 1,
SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID())) % (ASCII('Z')-ASCII('A')+1) +ASCII('A'))
+ CHAR(ABS(CHECKSUM(NEWID())) % (ASCII('Z')-ASCII('A')+1) +ASCII('A')),
SomeMoney = CAST(RAND(CHECKSUM(NEWID())) * 100 AS DECIMAL(9,2)), --Note rounding
SomeDateTime = RAND(CHECKSUM(NEWID())) * DATEDIFF(dd,'2000','2020') + CAST('2000' AS DATETIME),
SomeDate = ABS (CHECKSUM(NEWID())) % DATEDIFF(dd,'2000','2020') + CAST('2000' AS DATETIME),
SomeName = RIGHT(NEWID(),ABS(CHECKSUM(NEWID())) % 19 + 2)
INTO dbo.JBMTest
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;
ALTER TABLE dbo.JBMTest
ADD CONSTRAINT PK_JBMTest PRIMARY KEY CLUSTERED (SomeID) WITH FILLFACTOR = 90
;
Here's the code being tested. Note that I did have to modify your code to work with DATETIME. The modifications were just to remove the CASTs to DATETIME2 that you did. Please check it to ensure that I faithfully kept the essence of the DATETIME2 compatible code.
RAISERROR('========== Simple date addition ==========',0,1) WITH NOWAIT;
DECLARE @TimeToAdd CHAR(12),
@Bitbucket DATETIME;
SELECT @TimeToAdd = '41:41:41.041';
SET STATISTICS TIME ON;
SELECT @Bitbucket = DATEADD(hh, ABS(LEFT(@TimeToAdd,2)), SomeDateTime+('00:'+RIGHT(@TimeToAdd,9)))
FROM dbo.JBMTest;
SET STATISTICS TIME OFF;
GO
RAISERROR('========== DATETIME2 Compatible ==========',0,1) WITH NOWAIT;
DECLARE @TimeToAdd CHAR(12),
@Bitbucket DATETIME;
SELECT @TimeToAdd = '41:41:41.041';
SET STATISTICS TIME ON;
SELECT @Bitbucket = DATEADD(ms, 1, DATEADD(hour, CAST(LEFT(@TimeToAdd, 2) AS INT)
,DATEADD(ms
,DATEDIFF(ms, 0, '00:'+RIGHT(@TimeToAdd, 9))
,SomeDateTime)))
FROM dbo.JBMTest;
SET STATISTICS TIME OFF;
I'm sure that someone can write even faster code to demonstrate the performance advantage but here are the results I get on my older desktop box using SQL Server 2005 DE.
========== Simple date addition ==========
SQL Server Execution Times:
CPU time = 1156 ms, elapsed time = 1350 ms.
========== DATETIME2 Compatible ==========
SQL Server Execution Times:
CPU time = 1625 ms, elapsed time = 1799 ms.
Of course, since we're adding the same amount of time to all the rows, it's also much more efficient to preconvert the string to a DATETIME and then use some even simpler date addition.
RAISERROR('========== Preconverted date addition ==========',0,1) WITH NOWAIT;
DECLARE @TimeToAdd CHAR(12),
@Bitbucket DATETIME,
@TimeToAddDT DATETIME;
SELECT @TimeToAdd = '41:41:41.041';
SET STATISTICS TIME ON;
SELECT @TimeToAddDT = DATEADD(hh, ABS(LEFT(@TimeToAdd,2)),('00:'+RIGHT(@TimeToAdd,9)))
SELECT @Bitbucket = SomeDateTime + @TimeToAddDT
FROM dbo.JBMTest;
SET STATISTICS TIME OFF;
GO
========== Preconverted date addition ==========
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 844 ms, elapsed time = 1007 ms.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 21, 2013 at 11:01 pm
Jeff,
When I try this:
SELECT GETDATE()
,DATEADD(hh, ABS(LEFT('41:41:41.041',2)), GETDATE()+('00:'+RIGHT('41:41:41.041',9)))
I get these results:
2013-02-22 12:54:58.843 2013-02-24 06:36:39.883
When I do the math 843+041=884 - so what's up with that 883 result? I tried it several times and it seemed to be consistently off by 1 ms.
The only reason I went to DATETIME2 was thinking that the extra precision would take care of the 1 millisecond issue that arises when DATETIME is used with accuracy to the 3.3 ms you mentioned earlier. It turned out it didn't work for some unfathomable reason, hence my kludge of adding 1 ms to the result.
I do like the simple elegance of your solution though (converting timetoadd to a DATETIME beforehand was particularly crafty), not to mention the high praise deserved for all the work building the test harness. If it weren't for that pesky 1 ms of inaccuracy, I'd give you a +1 (instead only a +0.95). 🙂
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
February 21, 2013 at 11:21 pm
Oh yes. And I also noticed that your conversion of my DATETIME2 code causes the same 1ms of inaccuracy.
So presumably, if we're willing to live with 1ms of inaccuracy, we can change the DATETIME2 compatible version to this:
RAISERROR('========== DATETIME2 Compatible ==========',0,1) WITH NOWAIT;
DECLARE @TimeToAdd CHAR(12),
@Bitbucket DATETIME;
SELECT @TimeToAdd = '41:41:41.041';
SET STATISTICS TIME ON;
SELECT @Bitbucket = DATEADD(hour, CAST(LEFT(@TimeToAdd, 2) AS INT)
,DATEADD(ms
,DATEDIFF(ms, 0, '00:'+RIGHT(@TimeToAdd, 9))
,SomeDateTime))
FROM dbo.JBMTest;
SET STATISTICS TIME OFF;
Which results in a bit closer horse race:
========== Simple date addition ==========
SQL Server Execution Times:
CPU time = 266 ms, elapsed time = 271 ms.
========== DATETIME2 Compatible ==========
SQL Server Execution Times:
CPU time = 280 ms, elapsed time = 291 ms.
Mine still loses mind you but not by as much and I never mind losing to a champion in the performance arena such as yourself!
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
February 22, 2013 at 6:53 pm
dwain.c (2/21/2013)
The only reason I went to DATETIME2 was thinking that the extra precision would take care of the 1 millisecond issue that arises when DATETIME is used with accuracy to the 3.3 ms you mentioned earlier. It turned out it didn't work for some unfathomable reason, hence my kludge of adding 1 ms to the result.
BWAAA-HAAAA!!!! My bad, my mistake, and my apologies. I made a serious mistake in thinking your good code was "DATETIME2" compatible. I flat forgot that if the date part of date/time functions, such as DATEADD, is a string literal, then the function will return a DATETIME datatype which has the 3.3 millisecond rounding "problem". That means the 3rd digit of milliseconds will always be 0, 3, or 7. I f you want DATEADD to return a DATETIME2, then you have to explicitly case the string literal in the date parameter of DATEADD as DATETIME2 or it will return a DATETIME with the same rounding "error" I just spoke of.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 23, 2013 at 12:25 am
Jeff Moden (2/22/2013)
dwain.c (2/21/2013)
The only reason I went to DATETIME2 was thinking that the extra precision would take care of the 1 millisecond issue that arises when DATETIME is used with accuracy to the 3.3 ms you mentioned earlier. It turned out it didn't work for some unfathomable reason, hence my kludge of adding 1 ms to the result.BWAAA-HAAAA!!!! My bad, my mistake, and my apologies. I made a serious mistake in thinking your good code was "DATETIME2" compatible. I flat forgot that if the date part of date/time functions, such as DATEADD, is a string literal, then the function will return a DATETIME datatype which has the 3.3 millisecond rounding "problem". That means the 3rd digit of milliseconds will always be 0, 3, or 7. I f you want DATEADD to return a DATETIME2, then you have to explicitly case the string literal in the date parameter of DATEADD as DATETIME2 or it will return a DATETIME with the same rounding "error" I just spoke of.
No apologies necessary!
You know, I explored this a little more and it seems like there might be a bug in DATEADD. When you add the ms derived from the TIME component to a DATETIME2, it looks like the function is internally doing the arithmetic as a DATETIME.
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
February 23, 2013 at 12:36 am
Strike that last post. The issue seems to be here:
SELECT GETDATE(), DATEDIFF(millisecond, 0, CAST('00:'+RIGHT('41:41:41.041', 9) AS TIME))
Where DATEDIFF should be returning 2501041 instead of 2501040.
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
February 23, 2013 at 1:01 am
Ahhhh! Now I've got it without the fudge factor.
DECLARE @TimeAdd VARCHAR(20) = '41:41:41.041'
SELECT GETDATE()
,DATEADD(hour
,ABS(LEFT(@TimeAdd, 2))
,DATEADD(millisecond
,DATEDIFF(millisecond
,CAST('1900-01-01' AS DATETIME2)
,CAST('00:' + RIGHT(@TimeAdd, 9) AS DATETIME2))
,CAST(GETDATE() AS DATETIME2)))
The problem above is that 0 in the DATEDIFF was being cast to 1900-01-01 00:00:00.000 (DATETIME), which type apparently has a higher precedence than DATETIME2, hence DATEDIFF was proceeding with the arithmetic in DATETIME.
I feel much better.
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
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply