locked
smalldate time conversion problem RRS feed

  • Question

  • I wonder if someone can shed some light on this?  The query below runs if you take off the "and convert(smalldatetime,matdate) >= @GT18Mo" line.  So in general ms sql is happy with the data in the matdate column.  matdate is declared varchar(8) and looks like 20110501.  I will say something even stronger.  The query below runs if you change the comparison operator to <> on the and condition above.  Anybody have any ideas as to why it would run with the comparison operate <> and not when the comparison operator is >=?

    set dateformat ymd
     declare @Curdate smalldatetime, @GT18Mo smalldatetime, @xx smalldatetime

    set @Curdate=(select convert(smalldatetime,var_value+'01') from xdatetable where var_id=1)
    set dateformat ymd
    set @GT18Mo=(select dateadd(mm,19,@curdate))
    --select 'gt 18 mo ', @GT18Mo
    set dateformat ymd
    SELECT convert(smalldatetime,matdate)
    FROM tablea as a where a.fk_orgsorgid between 104855 and 105000 and
     TERM <> '0' and (source in ('RE') ) and TERM <> '0'  and a.officer=officer
    --and avgbal 5000
    --and spc='R2'
    and convert(smalldatetime,matdate) >= @GT18Mo
    order by avgbal
    --dbcc useroptions

    Thursday, January 6, 2011 6:52 PM

Answers

  • It may change it. Just post both plans (use SET SHOWPLAN_TEXT ON).

    See SHOWPLAN BOL Reference 

    Also, I know that Brad Schulz wrote several interesting blogs about OPTIMIZER and strange results. They may be not exactly revealing this particular problem, but in any way good blogs to read.

    Check this one, for example (I was looking for another one, but I haven't read this one myself yet)

    All I Want is a Normal(ized) Life


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by etlman Friday, January 7, 2011 9:55 PM
    Friday, January 7, 2011 6:21 PM

All replies

  • What is the datatype of matdate and what is the result of this query:

    select top 5 matdate
    from tableA
    order by matDate desc

     

    Thursday, January 6, 2011 7:09 PM
  • Kent,   Our tables are huge.  There are many matdates with the same value.  Here is the result of your query:

    20991231
    20991231
    20991231
    20991231
    20991231

    I would argue if you wanted to boil this question  to it's essence and be able to replicate in your environment, create a 1 column table with matdate declared varchar(8), fill in a few rows with valid yyyymmdd formats and run the queries above on that.  By the way this ms sql 2005.

    Thursday, January 6, 2011 7:23 PM
  • Kent,   Our tables are huge.  There are many matdates with the same value.  Here is the result of your query:

    20991231
    20991231
    20991231
    20991231
    20991231

    I would argue if you wanted to boil this question  to it's essence and be able to replicate in your environment, create a 1 column table with matdate declared varchar(8), fill in a few rows with valid yyyymmdd formats and run the queries above on that.  By the way this ms sql 2005.


    Please give a look at the writeup for the SMALLDATETIME datatype in books online.  You will find that 20991231 is out of bounds and that you are receiving valid error messages.  Try the datetime datatype instead.
    • Proposed as answer by Naomi N Thursday, January 6, 2011 8:21 PM
    Thursday, January 6, 2011 7:39 PM
  • Hi,  I guess I withdraw this question.  I tried to create a situation where only the basics were involved: 

    create table xyz(matdate varchar(8))
    insert into xyz
    values ('20101101')
    insert into xyz
    values ('20130501')
    insert into xyz
    values ('20160718')
    set dateformat ymd
    declare @xx smalldatetime
    set @xx=convert(smalldatetime,'20090601')
    select convert(smalldatetime,matdate) from tomdev..xyz
    where convert(smalldatetime, matdate) >= @xx

    The above doesn't throw integer conversion error.  I even took rows that cause the problem from the original table and put them in the xzy table and the int conversion error still didn't occur.  So this problem only occurs with my mega million row table and my join criteria so I withdraw the question. 

     

    Thursday, January 6, 2011 9:02 PM
  • Fair enough; it would still be good if you would share what you did to either work around or resolve this issue.
    Thursday, January 6, 2011 9:11 PM
  • As Ken said, instead of convert to smalldate try converting to date and the error should go away.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Thursday, January 6, 2011 9:12 PM
  • Kent,  Good point.  My boss and I took the cowards way out and used a solution that would not always be available to others.  Since the varchar field that was loaded with yyyymmdd lent itself to a correct date compare we took the @GT18Mo variable and converted it to @GT18MoTxt (a varchar(8)) field and did the comparison as varchar to varchar rather than smalldatetime to smalldatetime.   yyyymmdd loaded with year then month then day will still give a correct date compare.  The code to set @GT18MoTxt variable looked like this:

    set

     

    @GT18Mo=(select dateadd(mm,19,@curdate))

    set

     

    @GT18MoTxt=convert(varchar(4),year(@GT18Mo))+right('00'+convert(varchar(2),month(@GT18Mo)),2)+right('00'+convert(varchar(2),day(@GT18Mo)),2)

    Thanks for your help.

    Thursday, January 6, 2011 9:39 PM
  • Naom,  Just as a point of information I did convert all the proc variables to datetime and it still threw the integer conversion error.

    Thursday, January 6, 2011 9:46 PM
  • I didn't mean the variables, I meant the code in the select that operated with the varchar data. In other words, you posted 5 top values from the table which clearly were out of Smalldate data range. In means you can not convert it to smalldatetime and need to convert to datetime instead.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Thursday, January 6, 2011 9:49 PM
  • Funny, you read something and you realize you missed explaining a big part of the problem.  This the error message I get when I run the query with >=:


    Msg 296, Level 16, State 3, Line 9
    The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.

    Thursday, January 6, 2011 10:13 PM
  • This might lead to a clue that Kent was alluding to above.  Mayve a convert(smalldatetime,matdate) always does a conversion.  But when you use the variable for something, it decides and tells you that you are out of range.  That might explain why a select of convert(smalldatetime, matdate) works but referring to it in an and condition does not. 
    Thursday, January 6, 2011 10:20 PM
  • The SELECT happens after WHERE is evaluated. Take a look at the Anatomy of the Query

    http://pratchev.blogspot.com/2007/05/anatomy-of-query.html

    So, first we need to filter the results. Here the conversion is important. The SELECT happens way after, so it's possible that BAD data were already filtered.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Thursday, January 6, 2011 10:36 PM
  • Naom,

          OK, just for chuckles (and obviously you have to trust me on this) below I include the dates from the table that met the where criteria without the convert(smalldatetime, matdate) >= ... condition.   They look pretty normal to me.  Greater than 2009 and less than 2016 and I don't thing theres a 20xx0229 for a year that's not a leap year or a 20101131:

     

    20110415
    20110309
    20141113
    20101228
    20101217
    20120822
    20130516
    20131006
    20150401
    20110324
    20110414
    20110620
    20121003
    20110401
    20120129
    20110116
    20110401
    20121215
    20110616
    20110411
    20110317
    20120917

    Thursday, January 6, 2011 11:40 PM
  • They all look OK. However, please include the execution plan with and without condition. If there are bad data in the table, even if they are going to be filtered, you can not use conversion to smalldate - otherwise you'll introduce a very hard to find bug that may bite years later. Say, the execution plan changes and there is a table scan - you'll get an error.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Thursday, January 6, 2011 11:44 PM
  • Naom,

        OK, OK, I get it.  The final result set is not the dates we care about.  Depending on the execution plan we may try to convert a matdate that isn't actually in the final result set, depending on how the query is executed by ms sql.  Confession, there are actually a lot of values of matdate in this table that will blow up a convert, not just 20991231.  I read some of your web site but give me the Cliff notes version of execution plan.  I would think it would be something like keys with indexes first then the clauses closest to the where from left to right.  The query I gave is a simple WHERE clause.  For those of you following allow do a:

    insert into xyz

    values('20991231')

    and then do a select convert(smalldatetime, matdate) from xyz and you will see the date fail mesage. convert(datetime, matdate) does work.

    I still have one defense of my orignal postion.  How does changing >= to <> in one AND clause change the execution plan?

    Friday, January 7, 2011 5:54 PM
  • Naom,  Another question.  The obvious solutioin is to force the execution plan to work a certain way.  Is there a way do to that without 15 temp tables?
    Friday, January 7, 2011 5:56 PM
  • It may change it. Just post both plans (use SET SHOWPLAN_TEXT ON).

    See SHOWPLAN BOL Reference 

    Also, I know that Brad Schulz wrote several interesting blogs about OPTIMIZER and strange results. They may be not exactly revealing this particular problem, but in any way good blogs to read.

    Check this one, for example (I was looking for another one, but I haven't read this one myself yet)

    All I Want is a Normal(ized) Life


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by etlman Friday, January 7, 2011 9:55 PM
    Friday, January 7, 2011 6:21 PM
  • Noam,

        You da man!  This came out more readable here than I thought.  Two showplan's are shown.  If you look near the bottom of each showplan you can see the relevant ordering.  In the <> case the convert(smalldatetime, matdate) shows as the last evaluation done for the where clause, so we have winnied down to the 22 dates shown above and no problem.  If you look at the showplan for the >= case the TERM <> '0' shows as the last evaluation and the convert(smalldatetime, matdate) as the next to the last evaluation, so we could evaluate values of matdate that won't convert to dates.  So is there something you can say in your code that forces the execution plan to do the convert(smalldatetime, matdate) last?  Thanks for your help.               ... Flash 

     

     
    showplan for >= case
    SELECT convert(smalldatetime,matdate)  FROM tableA as a where a.fk_orgsorgid between 104855 and 105000 and   TERM <> '0' and (source in ('RE') )   and a.officer=officer  --and avgbal 5000   --and spc='R2'  and convert(smalldatetime,matdate) >= @GT18Mo  order by avgbal
      |--Sort(ORDER BY:([a].[AVGBAL] ASC))
           |--Compute Scalar(DEFINE:([Expr1003]=CONVERT(smalldatetime,[probdb].[dbo].[tableA].[MATDATE] as [a].[MATDATE],0)))
                |--Clustered Index Seek(OBJECT:([probdb].[dbo].[tableA].[tableA_LFT] AS [a]), SEEK:([a].[fk_OrgsOrgID] >= (104855) AND [a].[fk_OrgsOrgID] <= (105000)),  WHERE:([probdb].[dbo].[tableA].[OFFICER] as [a].[OFFICER]=[probdb].[dbo].[tableA].[OFFICER] as [a].[OFFICER] AND [probdb].[dbo].[tableA].[SOURCE] as [a].[SOURCE]='RE' AND CONVERT(smalldatetime,[probdb].[dbo].[tableA].[MATDATE] as [a].[MATDATE],0)>=[@GT18Mo] AND ([probdb].[dbo].[tableA].[TERM] as [a].[TERM]<'0' OR [probdb].[dbo].[tableA].[TERM] as [a].[TERM]>'0')) ORDERED FORWARD)

    showplan for <> case
     SELECT convert(smalldatetime,matdate)  FROM tableA as a where a.fk_orgsorgid between 104855 and 105000 and   TERM <> '0' and (source in ('RE') )   and a.officer=officer  --and avgbal 5000   --and spc='R2'  and convert(smalldatetime,matdate) <> @GT18Mo  order by avgbal
      |--Sort(ORDER BY:([a].[AVGBAL] ASC))
           |--Compute Scalar(DEFINE:([Expr1003]=CONVERT(smalldatetime,[probdb].[dbo].[tableA].[MATDATE] as [a].[MATDATE],0)))
                |--Clustered Index Seek(OBJECT:([probdb].[dbo].[tableA].[tableA_LFT] AS [a]), SEEK:([a].[fk_OrgsOrgID] >= (104855) AND [a].[fk_OrgsOrgID] <= (105000)),  WHERE:([probdb].[dbo].[tableA].[OFFICER] as [a].[OFFICER]=[probdb].[dbo].[tableA].[OFFICER] as [a].[OFFICER] AND [probdb].[dbo].[tableA].[SOURCE] as [a].[SOURCE]='RE' AND ([probdb].[dbo].[tableA].[TERM] as [a].[TERM]<'0' OR [probdb].[dbo].[tableA].[TERM] as [a].[TERM]>'0') AND (CONVERT(smalldatetime,[probdb].[dbo].[tableA].[MATDATE] as [a].[MATDATE],0)<[@GT18Mo] OR CONVERT(smalldatetime,[probdb].[dbo].[tableA].[MATDATE] as [a].[MATDATE],0)>[@GT18Mo])) ORDERED FORWARD)

    Friday, January 7, 2011 9:54 PM
  • There is a possibility to force a particular saved Query plan to be used. It should be listed in Query hints. I have never tried it myself and the whole topic is quite advanced. You may want to start from what we found, post a new thread in Database Design (or SQL Engine) forum or here to get more suggestions.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, January 7, 2011 10:16 PM