Problem with Datetime

Question

Problem with Datetime

Apurva

SSCrazy

Points: 2412
More actions
September 16, 2010 at 1:36 pm

#240500

Hi,
We started having a issue recently where the varchar value was not being converted to datetime. After investigating the problem I found that the C# application is sending this
'2010-09-10 11:14:21.7870000' value in the parameter. I concluded that the conversion issue is due to more than 3 digits in the millisecond part of the value (7870000).
If I remove the trailing zeros on the millisecond part then the conversion works fine.
We started having the problem with the test application which has been running fine till now. I went to see what is happening on production through the profiler and found that the sql server does receive values like '2010-09-10 11:14:21.7870000' and does not have any conversion issue there.
After researching online it looks that the sql server rounds of the milliseconds to 3 digits.
So Now I have few questions.
1) When i execute the query on production in SSMS , I get a conversion error because milliseconds has more than 3 digits. Why doesn't the application get an error on production when it sends the long milliseconds. Is the sql server doing automatic rounding?
2) Any ideas why the test has started having issues? Is there a setting which must have got flipped?
We are using sql server 2008
Regards,
Apurva

Viewing 3 posts - 1 through 2 (of 2 total)

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

Is it possible that in one place you're using a datetime datatype (which will not accept the extra digits), and in the other you're using datetime2 (which will)?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Apurva SSCrazy Points: 2412 More actions · Answer 2

No. I am suing datetime at both the places. I am wondering if SQL server is supposed to give error when it gets more than 3 digits milliseconds or is it supposed to round it off and restrict it to 3 digits on its own?