September 24, 2008 at 8:24 am
I was doing:
SELECT
*
FROM [db].[dbo].[Table]
WHERE Responsible = 'testusr'
AND StartDate >= Convert(Datetime, '2008/09/17 00:00:00:000')
AND StartDate <= Convert(Datetime, '2008/09/18 23:59:59:999') -- 2 days
And I know that it is better to use >= && <
SELECT
*
FROM [db].[dbo].[Table]
WHERE Responsible = 'testusr'
AND StartDate >= Convert(Datetime, '2008/09/17 00:00:00:000')
AND StartDate < Convert(Datetime, '2008/09/19 00:00:00:000') -- 2 days
But this should be the same, except that Convert(Datetime, '2008/09/17 23:59:59:999')== 2008-09-18 00:00:00.000
So even if you read that SQL Server 2005 only is exact 1/300th of a second (do 1 every 3 milliseconds get saved) it should still know that 23:59:59:999 is today and not tomorrow…
Same behavior is in SQL Server 2008.
I am sure this is documented and all, but still anoying...
-Mark
September 24, 2008 at 8:42 am
It's all about the rounding during the conversion from your character string to a datetime. As you noted SQL server is only acurrate to 1/300th of a second.
.999 is rounded up to .000 while .998 is rounded down to .997
-Luke.
September 24, 2008 at 9:48 am
No I understand why this happens, I would just have expected the SQL Server team to make one exception to that rule when it affect the last possible value, or just always floor so 999 would also become 997 because when doing date selections 997 would be more true than +1 day 000.
-Mark
September 24, 2008 at 1:52 pm
DateTime2 ?
N 56°04'39.16"
E 12°55'05.25"
September 24, 2008 at 2:18 pm
I didn't know about that one, thanks,
But,
1 weird name 🙂
2 this is SQL Server 2008
3 Still doesn't fix the current (in my eyes) wrong behavior
-Mark
September 24, 2008 at 2:33 pm
A datetime2 can hold values from 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999
See the 7 decimals?
N 56°04'39.16"
E 12°55'05.25"
September 24, 2008 at 3:15 pm
mark.nijhof
From BOL 2008 read the subject Datetime(Transact-SQL) I would say that Microsoft's developers have felt the same anguish (or puzzlement) about handling date and time data as you have expressed and many of us have had to sweat over how to handle properly. They have introduced among other new data types a Time, a Date, a Datetime2 and a Datetimeoffset as data types in 2008. In the discussion of the various data type it includes the ability to track time to an accuracy of 1 nanosecond (ODBC and OLEDB). I would image the new Date data type will be most useful to most people, but then again the old adage "It depends" has to be considered.
September 24, 2008 at 3:37 pm
Hi BitBucket,
Thanks for the reference, I'll have to read it 🙂 But why say DateTime2 and not BigDateTime? Anyway thanks again.
-Mark
September 24, 2008 at 5:59 pm
mark
But why say DateTime2 and not BigDateTime?
Who truly knows. I find the collective mind of Microsoft to be somewhat like a split personality. Occasionally the Marketing group wins out on a decision, and some time the developers, or they may even roll dice or throw darts at a dart board to arrive at these decisions. Either way they make the product which allows us to make a living, better and more powerful with each release so all I can do is encourage the Microsoft people to keep on going.
September 25, 2008 at 12:27 am
hehe No I agree with that, they do enable many of us, but without criticism / faults there will be less improvement.
-Mark
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply