Weird MS SQL "Error"
-
I ran this in the Management Studio: update [Master] set Data_Thru = '7/23/2014 9:49:00 AM' where Data_Thru = '7/23/2014 8:00:00 AM' So why did it actually set the smalldatetime to 7/23/2014 9:50:00 AM? (And don't yell at me for the table name. It wasn't my idea.)
We won't sit down. We won't shut up. We won't go quietly away. YouTube and My Mu[sic], Films and Windows Programs, etc.
-
I ran this in the Management Studio: update [Master] set Data_Thru = '7/23/2014 9:49:00 AM' where Data_Thru = '7/23/2014 8:00:00 AM' So why did it actually set the smalldatetime to 7/23/2014 9:50:00 AM? (And don't yell at me for the table name. It wasn't my idea.)
We won't sit down. We won't shut up. We won't go quietly away. YouTube and My Mu[sic], Films and Windows Programs, etc.
Are you absolutely sure that that's the precise query that you executed, and that the row you're looking at is the one it updated? smalldatetime[^] will round to the nearest minute, but it will only round up if the seconds are greater than or equal to 29.999; anything else will round down.
SELECT
CAST('7/23/2014 9:49:29.998 AM' as smalldatetime),
CAST('7/23/2014 9:49:29.999 AM' as smalldatetime)
;/*
Output:
2014-07-23 09:49:00
2014-07-23 09:50:00
*/
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Are you absolutely sure that that's the precise query that you executed, and that the row you're looking at is the one it updated? smalldatetime[^] will round to the nearest minute, but it will only round up if the seconds are greater than or equal to 29.999; anything else will round down.
SELECT
CAST('7/23/2014 9:49:29.998 AM' as smalldatetime),
CAST('7/23/2014 9:49:29.999 AM' as smalldatetime)
;/*
Output:
2014-07-23 09:49:00
2014-07-23 09:50:00
*/
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Richard Deeming wrote:
Are you absolutely sure that that's the precise query that you executed, and that the row you're looking at is the one it updated?
Yep. Freaked me out. I've never seen that before, and it had no problem with me going in and correcting it back down to 9:49 via T-Sql
We won't sit down. We won't shut up. We won't go quietly away. YouTube and My Mu[sic], Films and Windows Programs, etc.
-
Richard Deeming wrote:
Are you absolutely sure that that's the precise query that you executed, and that the row you're looking at is the one it updated?
Yep. Freaked me out. I've never seen that before, and it had no problem with me going in and correcting it back down to 9:49 via T-Sql
We won't sit down. We won't shut up. We won't go quietly away. YouTube and My Mu[sic], Films and Windows Programs, etc.
Weird. Are there any triggers on that table? Or other users updating the same row at the same time? :confused:
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Weird. Are there any triggers on that table? Or other users updating the same row at the same time? :confused:
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Nope. No triggers. And no users at all (yet). Maybe we'll have to pass it off as a cosmic ray hitting the server and flipping a bit at just the right time. I see no resason for it, and Google came up with nothing, either.
We won't sit down. We won't shut up. We won't go quietly away. YouTube and My Mu[sic], Films and Windows Programs, etc.
-
I ran this in the Management Studio: update [Master] set Data_Thru = '7/23/2014 9:49:00 AM' where Data_Thru = '7/23/2014 8:00:00 AM' So why did it actually set the smalldatetime to 7/23/2014 9:50:00 AM? (And don't yell at me for the table name. It wasn't my idea.)
We won't sit down. We won't shut up. We won't go quietly away. YouTube and My Mu[sic], Films and Windows Programs, etc.
-
I ran this in the Management Studio: update [Master] set Data_Thru = '7/23/2014 9:49:00 AM' where Data_Thru = '7/23/2014 8:00:00 AM' So why did it actually set the smalldatetime to 7/23/2014 9:50:00 AM? (And don't yell at me for the table name. It wasn't my idea.)
We won't sit down. We won't shut up. We won't go quietly away. YouTube and My Mu[sic], Films and Windows Programs, etc.
What's the version / localization of your SQL Server? I tried it with SQL Server 10.0.5500, German localized Management studio. I adjusted the date format of your query, and tested it. And it worked as it was expected to work - I cannot reproduce your bug.
-
What's the version / localization of your SQL Server? I tried it with SQL Server 10.0.5500, German localized Management studio. I adjusted the date format of your query, and tested it. And it worked as it was expected to work - I cannot reproduce your bug.
