problem with subtracting one millisecond

Question

Post reply

problem with subtracting one millisecond

phoenix_

SSChasing Mays

Points: 614
More actions
September 12, 2013 at 2:09 am

#282343

Hi,
what I am doing seems to be very simple. I just want to subtract one millisecond from midnight to receive as an output 23:59:59.999. Just before this step I am just calculating the last day of the week. Unfortunately when subtracting 1 millisecond then nothing happens but when subtracting 2 milliseconds the ms sql is subtracting 3. Am I doing something wrong?
1 millisecond:
DECLARE @TempDate AS DATETIME
SET @TempDate = '2013-09-12'
SELECT Convert(DateTime, DATEADD(millisecond, -1, DATEADD(ww, DATEDIFF(ww,0,@TempDate) + 1, 0)))
2013-09-16 00:00:00.000
2 milliseconds:
DECLARE @TempDate AS DATETIME
SET @TempDate = '2013-09-12'
SELECT Convert(DateTime, DATEADD(millisecond, -2, DATEADD(ww, DATEDIFF(ww,0,@TempDate) + 1, 0)))
2013-09-15 23:59:59.997

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

You need to use the data type datetime2 for this.

The data type datetime has a precision only up to 3ms, so there's no .999 precision.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

phoenix_ (9/12/2013)
... Am I doing something wrong?..

Possibly. If the datetime you are constructing is the upper bound of a range, you will almost certainly find it easier to use the date of the following day at zero time, with the < (less than) operator. Can you paste the code chunk where you're performing the date arithmetic?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

phoenix_ SSChasing Mays Points: 614 More actions · Answer 3

I am not doing calculations with such precision. I was just going to display the data with such accuracy. i.e. I have 2013-09-15 which is the end of the week but it's required to display it as 2013-09-15 23:59:59.999. While for calculations I am using < 2013-09-16.

So for displaying purposes I will use the hint with DATETIME2.

Thank you for your help.

Dwain Camps SSC Guru Points: 86908 More actions · Answer 4

Which of these is closer to the end of the week?

SELECT DATEADD(millisecond, -1, CAST('2013-09-13' AS DATETIME2))

,DATEADD(microsecond, -1, CAST('2013-09-13' AS DATETIME2))

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
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

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 5

dwain.c (9/12/2013)
Which of these is closer to the end of the week?
SELECT DATEADD(millisecond, -1, CAST('2013-09-13' AS DATETIME2))
,DATEADD(microsecond, -1, CAST('2013-09-13' AS DATETIME2))

Too bad SQL Server doesn't support nanoseconds yet.

Maybe with a datetime3... 😀

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Koen Verbeeck (9/13/2013)
dwain.c (9/12/2013)
Which of these is closer to the end of the week?
SELECT DATEADD(millisecond, -1, CAST('2013-09-13' AS DATETIME2))
,DATEADD(microsecond, -1, CAST('2013-09-13' AS DATETIME2))
Too bad SQL Server doesn't support nanoseconds yet.
Maybe with a datetime3... 😀

Almost 🙂

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden