A SQL Server Date Handling Issue

  • dhaveedh

    SSC Enthusiast

    Points: 126

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

  • trboyden

    SSC Veteran

    Points: 263

    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: 1434

    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

    SSC Eights!

    Points: 880

    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

    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

    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: 183567

    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

    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: 161

    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: 161

    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: 1573

    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: 183567

    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: 1573

    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

    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: 994661

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

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

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