June 20, 2016 at 7:05 pm
This was definitely wrong on many levels, but what strikes me is that they are expecting to extract a time from the first 8 characters of a variable called StartDate. Even if it had been a DateTime variable, the first 8 characters will always be the date, and they should never expect to get anything other than an ID of 2.
Personally, if I was expecting a time, my variable would probably be called StartTime. š
I suppose I can conceive of a scenario where the procedure was intended to be generic and receive data from many different routines, passing it a variety of date and/or time formatted strings, and the only time you wanted an ID other than 2 was when it was actually passed a time, but Iām pretty sure anyone reading this could come up with a better, safer way.
June 20, 2016 at 7:48 pm
Judging by the low marks of this article and people saying the "OP" wrote "stupid" code, I think that a lot of people think the author wrote this code.
The author didn't write this code. He was troubleshooting it and wanted to share some of his findings. The row about the Leap Year wasn't about what he would do... it was about the fact that little nuance caused a problem allowing him to find the "stupid code" and I agree... the code was stupid but, again, the author didn't write it. He was troubleshooting it.
I gave the article 5 stars because the author published the steps that he took during troubleshooting a problem he'd not seen before and pointed out why the failure had "worked" without error for so long. All very valuable lessons from his troubleshooting all the way back to the insanity that led to the code the author was troubleshooting. For newbies especially, the explanations really do explain (if not, strongly suggest) why code like that shouldn't be written to begin with, especially because SQL Server "handled it" until it couldn't.
On the other side of the house, this also shows the importance of peer review, testing, and QA. If you haven't hugged someone in QA today, do it tomorrow because they would have tested this and not allowed it to go through. š
@david-2 Ozono... Nice article and thank you for taking the time to put it together. Keep up the good work and keep sharing. This was good stuff even if some folks thought you were the culprit.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 21, 2016 at 3:18 am
Ken Hiatt (6/20/2016)
This was definitely wrong on many levels, but what strikes me is that they are expecting to extract a time from the first 8 characters of a variable called StartDate. Even if it had been a DateTime variable, the first 8 characters will always be the date, and they should never expect to get anything other than an ID of 2.Personally, if I was expecting a time, my variable would probably be called StartTime. š
I suppose I can conceive of a scenario where the procedure was intended to be generic and receive data from many different routines, passing it a variety of date and/or time formatted strings, and the only time you wanted an ID other than 2 was when it was actually passed a time, but Iām pretty sure anyone reading this could come up with a better, safer way.
CONVERT ( data_type [ ( length ) ] , expression [ , style ] ) was the syntax used. Please refer to (https://msdn.microsoft.com/enGB/library/ms187928.aspx) for more information. Style 108 from a datetime value will give you the time portion.
June 21, 2016 at 6:39 am
Jeff Moden (6/20/2016)
trboyden (6/20/2016)
SQL Server expects the date to be passed in as "2016-06-20 00:00:00"That's not true at all. SQL Server will take a couple dozen date/time formats in stride with no explicit conversion at all, many of which aren't even listed in CONVERT. It certainly does NOT require that particular date format as an input to a date/time data type. This assumes the DATE FORMAT MDY setting.
Here's a simple test that proves at least 26 of the different possibilities.
Good for you, you can list datetime formats based on the MSDN date/time formatting chart. Doesn't change the fact that the OP's application was pulling dates in the wrong format ('18-AUG-2'), which even as you said, would have been caught if proper testing was done. Doesn't matter if he wrote the code or not. That's not what people are debating. The whole premise of his article was that this was some kind of SQL bug. It wasn't. It was an illustration of poor application design and a lack of testing/quality control.
June 21, 2016 at 6:46 am
Wouldn't that work in 2400 - not 4000? Divisible by 4 but not 400?
June 21, 2016 at 7:04 am
trboyden (6/21/2016)
Jeff Moden (6/20/2016)
trboyden (6/20/2016)
SQL Server expects the date to be passed in as "2016-06-20 00:00:00"That's not true at all. SQL Server will take a couple dozen date/time formats in stride with no explicit conversion at all, many of which aren't even listed in CONVERT. It certainly does NOT require that particular date format as an input to a date/time data type. This assumes the DATE FORMAT MDY setting.
Here's a simple test that proves at least 26 of the different possibilities.
Good for you, you can list datetime formats based on the MSDN date/time formatting chart. Doesn't change the fact that the OP's application was pulling dates in the wrong format ('18-AUG-2'), which even as you said, would have been caught if proper testing was done. Doesn't matter if he wrote the code or not. That's not what people are debating. The whole premise of his article was that this was some kind of SQL bug. It wasn't. It was an illustration of poor application design and a lack of testing/quality control.
I do not think that the article ever implied or attributed any bug to SQL Server. It was merely highlighting how SQL Server was dealing with the bad data presented to it. This only became evident when the script failed when presented with the date '29-Feb-2016'.
June 21, 2016 at 7:45 am
dhaveedh (6/21/2016)
I do not think that the article ever implied or attributed any bug to SQL Server. It was merely highlighting how SQL Server was dealing with the bad data presented to it. This only became evident when the script failed when presented with the date '29-Feb-2016'.
SELECT CAST('29-FEB-2'as DATE)This returns the value: 2002-02-29. This is not a valid day in the calendar because year 2002 is not a leap year. Hence the conversion to a datetime failed. Whereas 29 FEB 2016 is a valid day, [highlight="#ffff11"]SQL Server's attempt to use that date based on the condition was what actually caused it to fail...[/highlight]
Here you are blaming SQL Server for the data conversion result. SQL Server didn't make you pass an improperly formatted date string to it. I'm not saying it is intentional per se, probably just sloppy writing, but there is an example. Not to mention improper use of CAST versus CONVERT. Had the code been written as:
SELECT CONVERT(datetime, '29-FEB-2')
SQL Server would have slapped you and told you it was an improper date, which you could have traced to an improperly formatted date, by reviewing the MSDN datetime formatting documentation.
Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
June 21, 2016 at 7:59 am
Jeff Moden (6/20/2016)
I gave the article 5 stars because the author published the steps that he took during troubleshooting a problem he'd not seen before and pointed out why the failure had "worked" without error for so long.
On the other side of the house, this also shows the importance of peer review, testing, and QA. If you haven't hugged someone in QA today, do it tomorrow because they would have tested this and not allowed it to go through. š
I couldn't agree more; I try to work with my junior developers on their assignments and am getting quite adept at the devil's advocate role. Our techniques may differ but we learn from one another in this way.
And on a tangent, one of the Apps that i use has issues based on how I have my DateTime displayed; using the 24h clock. Any times it is after 1PM (1300) it throws content expired errors.
June 21, 2016 at 10:36 am
Jeff Moden (6/20/2016)
Judging by the low marks of this article and people saying the "OP" wrote "stupid" code, I think that a lot of people think the author wrote this code.The author didn't write this code. He was troubleshooting it and wanted to share some of his findings. The row about the Leap Year wasn't about what he would do... it was about the fact that little nuance caused a problem allowing him to find the "stupid code" and I agree... the code was stupid but, again, the author didn't write it. He was troubleshooting it.
I gave the article 5 stars because the author published the steps that he took during troubleshooting a problem he'd not seen before and pointed out why the failure had "worked" without error for so long. All very valuable lessons from his troubleshooting all the way back to the insanity that led to the code the author was troubleshooting. For newbies especially, the explanations really do explain (if not, strongly suggest) why code like that shouldn't be written to begin with, especially because SQL Server "handled it" until it couldn't.
On the other side of the house, this also shows the importance of peer review, testing, and QA. If you haven't hugged someone in QA today, do it tomorrow because they would have tested this and not allowed it to go through. š
@david-2 Ozono... Nice article and thank you for taking the time to put it together. Keep up the good work and keep sharing. This was good stuff even if some folks thought you were the culprit.
Agreed. I thought this was interesting.
June 21, 2016 at 12:58 pm
dhaveedh (6/21/2016)
Ken Hiatt (6/20/2016)
This was definitely wrong on many levels, but what strikes me is that they are expecting to extract a time from the first 8 characters of a variable called StartDate. Even if it had been a DateTime variable, the first 8 characters will always be the date, and they should never expect to get anything other than an ID of 2.Personally, if I was expecting a time, my variable would probably be called StartTime. š
I suppose I can conceive of a scenario where the procedure was intended to be generic and receive data from many different routines, passing it a variety of date and/or time formatted strings, and the only time you wanted an ID other than 2 was when it was actually passed a time, but Iām pretty sure anyone reading this could come up with a better, safer way.
CONVERT ( data_type [ ( length ) ] , expression [ , style ] ) was the syntax used. Please refer to (https://msdn.microsoft.com/enGB/library/ms187928.aspx) for more information. Style 108 from a datetime value will give you the time portion.
Got it. So the fix could be as simple as adding the CAST function to make sure that StartDate is compatible with the 108 style.
WHERE CONVERT(CHAR(8),CAST(StartDate AS DateTime),108) BETWEEN FROM_TMS AND TO_TMS
Of course, there's still no time in the StartDate column data, so while it won't crash anymore, you still only get ID 2.
Thx
June 21, 2016 at 1:09 pm
Ken Hiatt (6/21/2016)
dhaveedh (6/21/2016)
Ken Hiatt (6/20/2016)
This was definitely wrong on many levels, but what strikes me is that they are expecting to extract a time from the first 8 characters of a variable called StartDate. Even if it had been a DateTime variable, the first 8 characters will always be the date, and they should never expect to get anything other than an ID of 2.Personally, if I was expecting a time, my variable would probably be called StartTime. š
I suppose I can conceive of a scenario where the procedure was intended to be generic and receive data from many different routines, passing it a variety of date and/or time formatted strings, and the only time you wanted an ID other than 2 was when it was actually passed a time, but Iām pretty sure anyone reading this could come up with a better, safer way.
CONVERT ( data_type [ ( length ) ] , expression [ , style ] ) was the syntax used. Please refer to (https://msdn.microsoft.com/enGB/library/ms187928.aspx) for more information. Style 108 from a datetime value will give you the time portion.
Got it. So the fix could be as simple as adding the CAST function to make sure that StartDate is compatible with the 108 style.
WHERE CONVERT(CHAR(8),CAST(StartDate AS DateTime),108) BETWEEN FROM_TMS AND TO_TMSOf course, there's still no time in the StartDate column data, so while it won't crash anymore, you still only get ID 2.
Thx
A better option would have been to convert to time which would save processing time and keystrokes.
CONVERT(time, StartDate)
Obviously, the main problem remains until someone fixes the data in the StartDate column.
June 21, 2016 at 1:32 pm
Douglas Osborne-229812 (6/21/2016)
Wouldn't that work in 2400 - not 4000? Divisible by 4 but not 400?
No, that would become '29-feb-2' which translates as '2002-02-29'.
Only 22 dates will successfully convert in the datetime2 date range.
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
SELECT a.n FROM E a, E b
),
E4(n) AS(
SELECT a.n FROM E2 a, E2 b
),
cteTally(n) AS(
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
FROM E4
)
SELECT DATEADD( dd, -1, DATEADD( YY, n, CAST( '00010301' as datetime2)))
FROM cteTally
WHERE (1 + n) % 100 = (1 + n) / 1000
AND DAY(DATEADD( dd, -1, DATEADD( YY, n, CAST( '00010301' as datetime2)))) = 29;
June 23, 2016 at 7:45 am
trboyden (6/21/2016)
Jeff Moden (6/20/2016)
trboyden (6/20/2016)
SQL Server expects the date to be passed in as "2016-06-20 00:00:00"That's not true at all. SQL Server will take a couple dozen date/time formats in stride with no explicit conversion at all, many of which aren't even listed in CONVERT. It certainly does NOT require that particular date format as an input to a date/time data type. This assumes the DATE FORMAT MDY setting.
Here's a simple test that proves at least 26 of the different possibilities.
Good for you, you can list datetime formats based on the MSDN date/time formatting chart. Doesn't change the fact that the OP's application was pulling dates in the wrong format ('18-AUG-2'), which even as you said, would have been caught if proper testing was done. Doesn't matter if he wrote the code or not. That's not what people are debating. The whole premise of his article was that this was some kind of SQL bug. It wasn't. It was an illustration of poor application design and a lack of testing/quality control.
No need for the sarcasm. You made an incorrect statement that newbies would misinterpret and I made sure they weren't mislead.
Shifting gears to the subject at hand, I absolutely agree that the original code (again, NOT written by the author) is a travesty but it's also the fault of SQL Server. Although it's necessary for month and day, no system that accepts character representations of date should ever allow a single digit year to pass as valid.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 23, 2016 at 8:31 am
Jeff Moden (6/23/2016)
No need for the sarcasm. You made an incorrect statement that newbies would misinterpret and I made sure they weren't mislead.
Shifting gears to the subject at hand, I absolutely agree that the original code (again, NOT written by the author) is a travesty but it's also the fault of SQL Server. Although it's necessary for month and day, no system that accepts character representations of date should ever allow a single digit year to pass as valid.
Which is of course incorrect, because SQL server does not accept single digit years, if the the correct functions are used. As clearly shown by multiple respondents, use of CONVERT shows that SQL server will error out on the result. The lack of proper programming technique and error handling cannot be blamed on the application.
It's quite alright though, keep writing your buggy code. I make plenty of money fixing lazy programming.
June 23, 2016 at 9:13 am
trboyden (6/23/2016)
Jeff Moden (6/23/2016)
No need for the sarcasm. You made an incorrect statement that newbies would misinterpret and I made sure they weren't mislead.
Shifting gears to the subject at hand, I absolutely agree that the original code (again, NOT written by the author) is a travesty but it's also the fault of SQL Server. Although it's necessary for month and day, no system that accepts character representations of date should ever allow a single digit year to pass as valid.
Which is of course incorrect, because SQL server does not accept single digit years, if the the correct functions are used. As clearly shown by multiple respondents, use of CONVERT shows that SQL server will error out on the result. The lack of proper programming technique and error handling cannot be blamed on the application.
It's quite alright though, keep writing your buggy code. I make plenty of money fixing lazy programming.
In your last post you said SELECT CONVERT(Datetime, '29-Feb-2') returns an error because SQL Server does not accept single digit years?
Why does SELECT CONVERT(Datetime, '28-Feb-2') return 2002-02-28 00.00.00.000 without any failure? Maybe I missed something from your previous explanation.
Viewing 15 posts - 16 through 30 (of 38 total)
You must be logged in to reply to this topic. Login to reply