A SQL Server Date Handling Issue

Question

A SQL Server Date Handling Issue

dhaveedh

SSC Enthusiast

Points: 126
More actions
June 19, 2016 at 9:06 am

#327646

Comments posted to this topic are about the item A SQL Server Date Handling Issue

Viewing 15 posts - 1 through 15 (of 38 total)

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

trboyden SSC Veteran Points: 263 More actions · Answer 1

I wouldn't call it a SQL Server issue. SQL Server expects the date to be passed in as "2016-06-20 00:00:00" which is the standard SQL Server datetime format. So if the application isn't doing that, then that is a programmer bug, not a SQL Server bug. Garbage in, garbage out.

hjp Default port Points: 1437 More actions · Answer 2

trboyden (6/20/2016)
I wouldn't call it a SQL Server issue. SQL Server expects the date to be passed in as "2016-06-20 00:00:00" which is the standard SQL Server datetime format. So if the application isn't doing that, then that is a programmer bug, not a SQL Server bug. Garbage in, garbage out.

I couldn't agree more!

The OP shouldn't have gone as far as finding out about missing leap-years in 2002 or implicit datatype conversions from string to datetime, as interesting as these may be.

The statement WHERE CONVERT(CHAR(8), StartDate,108) BETWEEN FROM_TMS AND TO_TMS is the true error - and a really stupid one at that!

It is clear from the example, that [FROM_TMS] and [TO_TMS] are not datetime, but TIME datatypes! Assuming that we can trust the name of StartDate to be a datetime field (or even a date field?), taking the first 8 characters from the left returns nonsense. Using "108" yields unexpected results, but no parameter-value would save the day. The programmer has failed the course of writing T-SQL code big time! And I am not sure the topic deserves a post in this forum...

EDIT:

My apologies to the OP!

dhaveedh (6/20/2016)
If you run the the following query
SELECT CONVERT(CHAR(8), GETDATE(),108) : You will get data in the form hh:mi:ss

I committed the great sin of not looking up the value "108" in MSDN! Now I follow the thought of the original programmer (who - as many points out - is not the OP), and it is NOT stupid, nor an error.

It's just bad code. Because it relies on assumptions about the data types: As the OP points out (I just didn't saw it the other day), the programmer sees time values in a field and treats it like a time data type field, when in reality it is a character string.

The difference is crucial when we apply the CONVERT function, because it requires datetime in order for "108" to work. Unfortunately, an error isn't thrown by this syntax mistake. But on the other hand, this is also a strength of T-SQL: It can cope with dirty data without behaving like a spoiled child. Whether this is good or bad is subject to opinion, but precaution is necessary - that much is certain.

mkdm Ten Centuries Points: 1093 More actions · Answer 3

Thanks for the article! Often we're in a hurry to "make it work", and when we don't get the anticipated results, we just "try something else". It's almost always worth the time to understand why our code did something unexpected. 🙂

dhaveedh SSC Enthusiast Points: 126 More actions · Answer 4

As expected a lot of people will be stuck on the fact that the code was wrong in the first place. That is not without question. However, the interesting thing to me was how SQL Server dealt with the issue, this is what I thought was worth sharing.

trboyden SSC Veteran Points: 263 More actions · Answer 5

It's not so much a critique of the original code which I would expect you would understand needs refactoring. However, one should expect strange outcomes when data is passed to the system in a non-conforming manner. Again, the issue isn't with the SQL server software, it's just doing it's best to process the bad data being passed to it. A programmer should always be explicit in the data types and the data content being passed to the system. Leaving it up to the system to interpret bad data, is always a poor approach. There is no mystery here; as your own debug output shows, SQL told you how it was interpreting your data. It was up to you to transform it into the format it is expecting, to get the desired output. This is why I always recommend TDD (Test Driven Development). If you practice that faithfully, you won't ever have surprises, and you have the test cases built in to prove out your work.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

The code mentioned here isn't the problem. The problem comes from the database design. Dates shouldn't be stored as strings or integers. We also need to know if the time and date are different attributes or a single one.

Having a good design, would allow to remove all functions from the join conditions and give better performance to the queries.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

dhaveedh SSC Enthusiast Points: 126 More actions · Answer 7

trboyden (6/20/2016)
It's not so much a critique of the original code which I would expect you would understand needs refactoring. However, one should expect strange outcomes when data is passed to the system in a non-conforming manner. Again, the issue isn't with the SQL server software, it's just doing it's best to process the bad data being passed to it. A programmer should always be explicit in the data types and the data content being passed to the system. Leaving it up to the system to interpret bad data, is always a poor approach. There is no mystery here; as your own debug output shows, SQL told you how it was interpreting your data. It was up to you to transform it into the format it is expecting, to get the desired output. This is why I always recommend TDD (Test Driven Development). If you practice that faithfully, you won't ever have surprises, and you have the test cases built in to prove out your work.

Garbage In, Garbage Out is a fact. However I believe whoever wrote the statement was expecting a Datetime value in the StartDate column, from which they wanted only the time portion using the parameter value 108.

As I said in the article, it was only when a leap year was encountered that the script failed. So it could have passed testing depending on what data was presented for testing.

If you run the the following query

SELECT CONVERT(CHAR(8), GETDATE(),108) : You will get data in the form hh:mi:ss

johan.lindell SSC Enthusiast Points: 162 More actions · Answer 8

Changing the CONVERT to CHAR(11) instead of CHAR(8) does the trick. This, I guess, is the real error that should be solved.

SELECT CAST (CONVERT (CHAR(11), '29-Feb-2016', 108) as Date)

johan.lindell SSC Enthusiast Points: 162 More actions · Answer 9

First convert the date to a date time and then convert the result produces the expected result.

SELECT CAST ('29-Feb-2016' as datetime)

SELECT CONVERT (CHAR(20), CAST ('29-Feb-2016' as datetime), 108)

SELECT CONVERT (CHAR(8), CAST ('29-Feb-2016' as datetime), 108)

Mad Myche SSCommitted Points: 1593 More actions · Answer 10

Luis Cazares (6/20/2016)
The code mentioned here isn't the problem. The problem comes from the database design. Dates shouldn't be stored as strings or integers. We also need to know if the time and date are different attributes or a single one.
Having a good design, would allow to remove all functions from the join conditions and give better performance to the queries.

The db design is fine; as the data is stored as Time. The Stored Procedure and presumably the calling function are passing DateTime data as Varchar.

Director of Transmogrification Services

Luis Cazares SSC Guru Points: 183706 More actions · Answer 11

Mad Myche (6/20/2016)
Luis Cazares (6/20/2016)
The code mentioned here isn't the problem. The problem comes from the database design. Dates shouldn't be stored as strings or integers. We also need to know if the time and date are different attributes or a single one.
Having a good design, would allow to remove all functions from the join conditions and give better performance to the queries.
The db design is fine; as the data is stored as Time. The Stored Procedure and presumably the calling function are passing DateTime data as Varchar.

From the article:

the StartDate column was a VARCHAR(20)

So no, the db design is not fine. Even as datetime, it would be wrong if time is going to be used separate from the date.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Mad Myche SSCommitted Points: 1593 More actions · Answer 12

Luis Cazares (6/20/2016)
Mad Myche (6/20/2016)
Luis Cazares (6/20/2016)
The code mentioned here isn't the problem. The problem comes from the database design. Dates shouldn't be stored as strings or integers. We also need to know if the time and date are different attributes or a single one.
Having a good design, would allow to remove all functions from the join conditions and give better performance to the queries.
The db design is fine; as the data is stored as Time. The Stored Procedure and presumably the calling function are passing DateTime data as Varchar.
From the article:
the StartDate column was a VARCHAR(20)
So no, the db design is not fine. Even as datetime, it would be wrong if time is going to be used separate from the date.

I'll concede that I should have read all of the specs, I completely missed that the text was coming from a second table.

StartDate I could go either way on; depending on other methods that may be calling the data

Director of Transmogrification Services

dhaveedh SSC Enthusiast Points: 126 More actions · Answer 13

LC

I quite agree that the table should not be using the varchar (20) field to store the date values.

What surprised me was how sql server interpreted the wrong data passed in. It did it's own conversion resulting in the selection of a Time_ID value of 1 as per the TIME table and only failed when it could not resolve it as a valid date in a year.

I thought it would not work outright.

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 14

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.

--===== Create a test table to test different date formats with.

CREATE TABLE #DateTest (SomeDateTime DATETIME)

;

--===== Insert dates in 26 different formats with no explicit conversion

INSERT INTO #DateTest (SomeDateTime)

SELECT '6/10/1971' UNION ALL

SELECT '06/10/1971' UNION ALL

SELECT '6-10-1971' UNION ALL

SELECT '6.10.1971' UNION ALL

SELECT 'June 10 1971' UNION ALL

SELECT 'Jun 10 1971' UNION ALL

SELECT 'June 10, 1971' UNION ALL

SELECT 'Jun 10, 1971' UNION ALL

SELECT '10 June 1971' UNION ALL

SELECT '10 Jun 1971' UNION ALL

SELECT '1971-06-10' UNION ALL

SELECT '1971-6-10' UNION ALL

SELECT '1971/06/10' UNION ALL

SELECT '1971.06.10' UNION ALL

SELECT '19710610' UNION ALL

SELECT '6/10/71' UNION ALL

SELECT '06/10/71' UNION ALL

SELECT '6-10-71' UNION ALL

SELECT '6.10.71' UNION ALL

SELECT 'June 10 71' UNION ALL

SELECT 'Jun 10 71' UNION ALL

SELECT 'June 10, 71' UNION ALL

SELECT 'Jun 10, 71' UNION ALL

SELECT '10 June 71' UNION ALL

SELECT '10 Jun 71'

;

--===== They all "flew" and correctly created "1971-06-10 00:00:00.000"

SELECT * FROM #DateTest

;

If you're saying that the "2016-06-20 00:00:00" non ambiguous style is what should be passed, it's ok for humans but it's still not the ISO format.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)