July 12, 2012 at 2:02 pm
Why do the following scripts return unexpected results?
/* version Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Jun 17 2011 00:54:03 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) */
select DATEADD(ms, -1, CONVERT(datetime,dateadd(dd,1,convert(date,getdate()))))
/* returns 2012-07-13 00:00:00.000 */
/* expect 2012-07-12 23:59:59.999 */
select DATEADD(ms, -2, CONVERT(datetime,dateadd(dd,1,convert(date,getdate()))))
/* returns 2012-07-12 23:59:59.997 */
/* expect 2012-07-12 23:59:59.998 */
select DATEADD(ms, -3, CONVERT(datetime,dateadd(dd,1,convert(date,getdate()))))
/* returns 2012-07-12 23:59:59.997 */
I know there are other ways to do this, but I am curious as to why this doesn't work.
July 12, 2012 at 2:09 pm
Need to learn to let go, and use DATETIME2...
Works fine when converted to DATETIME2 instead of DATETIME.
Found my answer here:
http://msdn.microsoft.com/en-us/library/ms187819.aspx
Time range
00:00:00 through 23:59:59.997
July 12, 2012 at 2:27 pm
Just curious why you are subtracting 1 millisecond.
July 12, 2012 at 2:29 pm
Just to try a new way to get the end of a day for a date comparison.
Didn't want to do the boring old string concatenation...
July 12, 2012 at 2:34 pm
dkschill (7/12/2012)
Need to learn to let go, and use DATETIME2...Works fine when converted to DATETIME2 instead of DATETIME.
Found my answer here:
http://msdn.microsoft.com/en-us/library/ms187819.aspx
Time range
00:00:00 through 23:59:59.997
This might give you a more complete picture of the issue (from the same document you quoted, a little further down):
datetime values are rounded to increments of .000, .003, or .007 seconds, as shown in the following table.
In other words - DateTime is accurate to 3ms.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 12, 2012 at 2:36 pm
dkschill (7/12/2012)
Just to try a new way to get the end of a day for a date comparison.Didn't want to do the boring old string concatenation...
Date range comparisions should use a closed ended comparision on the lower of the range and an open ended comparision on the upper end of the range.
Example:
declare @StartDate datetime = '20120601';
declare @EndDate datetime = '20120701';
SELECT
...
FROM
...
WHERE
SomeDateCol >= @StartDate and SomeDateCol < @EndDate;
This will return all records where SomeDateCol in is June 2012.
July 12, 2012 at 2:37 pm
Awesome! Thanks for posting that. Most of the time I wouldn't care about 3ms, but I am building a monitoring tool and I would rather not have something slip through the cracks.
Thanks for the reply!
July 12, 2012 at 2:39 pm
I typically use BETWEEN; is that frowned upon? It just seems to look cleaner to me.
Though I can totally see how using the other way would require less pre-formatting.
/* Old */
select * from distribution.dbo.MSrepl_errors
where [time] between convert(datetime2,convert(date,dateadd(dd,-7,getdate()))) AND DATEADD(ms, -1, CONVERT(datetime2,dateadd(dd,1,convert(date,getdate()))))
/* New */
select * from distribution.dbo.MSrepl_errors
where
[time] >= CONVERT(date,dateadd(dd,-7,getdate()))
and [time] < CONVERT(date,dateadd(dd,1,getdate()))
July 12, 2012 at 2:44 pm
Between's fine, but requires more complex date maths and if someone were to change the data type from datetime to datetime or to change the precision of the datetime2, your upper bound maths will suddenly be wrong.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 12, 2012 at 2:47 pm
Why I am always so excited to find stuff to post here.
The feedback really is amazing stuff!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply