SQL2005 vs SQL2000 Compatibility issue

  • Hi All,

    I am executing the following query on SQL 2000 as well as SQL 2005

    but its behaving oddly in SQL 2000. It does not return me same results.

    In SQL 2005 it updates 13 rows whereas in SQL 2000 it updates no rows.

    The data as well as structure of tables is exactly the same since I detached the database on SQL 2000 and attached it on SQL 2005.

    Any idea on the same?



    SET @FEEDDATE = Convert(varchar, '01/21/2009', 101)


    UPDATE tblselectedrate

    SET Bid = tblCurrencyQuote.Bid,

    BankCodeBid = tblCurrencyQuote.BankCode,

    TimeBid = tblCurrencyQuote.Time,

    LowBid = tblCurrencyQuote.Bid,

    LowTimeBid = tblCurrencyQuote.Time,

    LowBankCode = tblCurrencyQuote.BankCode

    FROM tblselectedrate with (NOLOCK)

    INNER JOIN tblCurrencyQuote WITH (NOLOCK) ON tblselectedrate.ISOCurrencyCode = tblCurrencyQuote.ISOCurrencyCode

    INNER JOIN tblCurrency WITH (NOLOCK) ON tblCurrencyQuote.ISOCurrencyCode = tblCurrency.ISOCurrencyCode



    ( CONVERT(varchar(10),tblCurrencyQuote.[Time],108) BETWEEN CONVERT(varchar(10), DateAdd(hh, -@BSTVal, tblCurrency.StartTime), 108) AND CONVERT(varchar(10),DateAdd(hh, -@BSTVal, tblCurrency.EndTime),108))

    AND tblCurrencyQuote.Bid = (

    SELECT MIN (Bid) FROM tblCurrencyQuote tmp1 WITH (NOLOCK)


    ( CONVERT(varchar(10),tmp1.[Time],108) BETWEEN CONVERT(varchar(10),DateAdd(hh, -@BSTVal, tblCurrency.StartTime),108) AND CONVERT(varchar(10),DateAdd(hh, -@BSTVal, tblCurrency.EndTime),108))

    AND CONVERT(varchar(10),tmp1.[Time],101) = Convert(varchar(10),@FEEDDATE,101)

    AND tmp1.ISOCurrencyCode = tblCurrencyQuote.ISOCurrencyCode

    AND tmp1.ISOCurrencyCode = tblCurrency.ISOCurrencyCode

    AND tmp1.ISOCurrencyCode IN


    SELECT DISTINCT (tblselectedrate.ISOCurrencyCode)


    tblselectedrate WITH (NOLOCK)

    INNER JOIN tblcurrency WITH (NOLOCK) ON tblselectedrate.ISOCurrencyCode = tblcurrency.ISOCurrencyCode


    UPPER(LTRIM(RTRIM(tblcurrency.SourceBid))) = 'F'


    DATEDIFF(Day,tblselectedrate.FeedDate, @FEEDDATE) = 0


    (LEN(RTRIM(LTRIM(tblcurrency.RefCurrencyCode))) = 0 OR RTRIM(LTRIM(tblcurrency.RefCurrencyCode)) IS NULL)


    AND tmp1.Status = 0


    AND tblCurrencyQuote.Status = 0

    AND DATEDIFF(Day,tblselectedrate.FeedDate, @FEEDDATE) = 0

    AND DATEDIFF(Day,tblCurrencyQuote.[Time], @FEEDDATE) = 0


    Thanks in Advance...

  • Remove WITH (NOLOCK) and then compare.


  • I would check the settings for language and date first. There is a lot of date to string conversion going on, and my guess would be a language setting making nn/nn/nnnn meane either dd/mm/yyyy or mm/dd/yyyy. Eg near the start you are converting a string date to varchar, and then there is an implicit conversion to put it in the variable. There are lots of similar things happening later as well.

    Hope that helps


  • Thanks Mike...

    But I checked well all the date conversions where probably the code could crash down on different versions of SQL Server.

    Also checked with the date settings on the servers.

  • Which compatibility level are you running on the 2005 database?



  • I am using SQL Server 2000(80) as the compatibilty level for SQL 2005 database which is the same as in SQL 2000 database.

    One important point I would like to mention is that the results are appropriate on SQL 2005 database but there seems to be some error or bug in SQL 2000 database.

  • Just to be sure, you might want to go ahead and compare the tables between servers to be absolutely sure they are indeed identical. To quote Sherlock Holmes, "When all else is eliminated, whatever remains, however improbable, must be true".

    Never be afraid to question your assumptions, and see where that leads you.


    (aka smunson)


  • I am still fairly convinced that we have a date formatting issue here. EG:

    SET @FEEDDATE = Convert(varchar, '01/21/2009', 101)

    asks to convert 01/21/2009 to VARCHAR, using style 101, and there is then an imolicit conversion to datetime to convert to feeddate. So while the 101 style will be applied to the initial conversion it will not apply to the conversion to feeddate. I woul dbe happier if it read

    SET @FEEDDATE = Convert(datetime, '01/21/2009', 101)


    SET @FEEDDATE = '20090121' would be OK as it is unambiguous. All the formats that contain separators are subject to the date time confog settings in SQL Server so are always best avoided.

    Can you check that select @@language returns the same thing on both installations?


  • If the update works properly in the new 2005 environment, but not in the old 2000 environment I would take a practical approach and move everything to 2005. Then sit down, take a deep breath and delve into the 2000 database and examine what else went wrong and why. This way you prevent more errors while you search for the existing errors.



  • Hi! Try using this CAST(CONVERT(VARCHAR,yourdate,101) AS DATETIME)

  • Have you checked that the value of @BSTVal is being set correctly in both cases?

    I assume prcBSTOn is a proc, the code for which is not listed.

Viewing 11 posts - 1 through 10 (of 10 total)

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