Post reply

SQL query not running

  • March 9, 2009 at 1:30 am

    #135276

    Somthing strange is happening?!

    I have restored a SQL 2000 DB on to a new SQL 2005 DB.

    When I run:

    select top 100 * from tbldeals

    order by trade_date desc

    In get the results i want, i.e. data dated '2009-03-06 00:00:00'

    But when I run:

    select * from tbldeals

    where trade_date = '2009-03-06 00:00:00'

    I get no results!!!!???

    Is this somthing to do with the way i have restored the DB?

  • March 9, 2009 at 2:36 am

    #955823

    Please check whether all the data existed in 2000 has been restored or not (Do a Select count(*) from Table).

    Also check the database compatibility level is 90.

    EXEC sp_dbcmptlevel 'pubs', 90

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • March 9, 2009 at 2:49 am

    #955830

    Thanks for the info.

    When I run sp_dbcmptlevel 'Dealbookv2' against both DB's (2000 & 2005)

    I get The current compatibility level is 80.

    Is this correct?

  • March 9, 2009 at 2:52 am

    #955831

    Correct. Please try changing the compatibility level of the SQL 2005 database to "90".

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • March 9, 2009 at 2:55 am

    #955832

    I think I misread you question.

    Please try using CONVERT as below.

    select * from tbldeals

    where CONVERT(VARCHAR(10),trade_date,111) = '2009/03/06'

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • March 9, 2009 at 2:58 am

    #955833

    lol no problem.

    I have changed the SQL 2005 DB compatibility level to "90".

    Also when I run you SQl:

    select * from tbldeals

    where CONVERT(VARCHAR(10),trade_date,111) = '2009/03/06'

    It works fine.... whats the problem?

    All i want to be able to do is:

    SELECT *

    FROM tbldeals

    WHERE trade_date = '2009/03/06'

  • March 9, 2009 at 3:02 am

    #955834

    I feel the date format of the servers differs. Please check it in "Control Panel --> Regional and language Options --> Customize --> Date".

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • March 9, 2009 at 3:17 am

    #955840

    The date format is the same on both Servers...

  • March 9, 2009 at 3:39 am

    #955846

    Try

    ...WHERE trade_date = '20090306'

  • March 9, 2009 at 3:49 am

    #955850

    Yes that works, how do i get it working with:

    WHERE trade_date = '2009-03-06'

  • March 9, 2009 at 4:17 am

    #955860

    Depending on what language you are configured for the date represeneted by the string '2009-03-06' could be march 6th or 3rd June. Safest option by far is to leave out the - and it will always be interepreted as YYYYMMDD, I guess you meant 6th March but the system is interpreting it as 3rd June.

    Mike John

  • March 9, 2009 at 5:06 am

    #955869

    You can find more details in Itzik Ben Gan's "Inside Microsoft SQL Server 2005— T-SQL Programming" - part of the Chapter 1 deals with the datetime "nuts and bolts"...

  • March 9, 2009 at 2:03 pm

    #956383

    Okay, we have a double post here with answers going in both. I suggest continuing any further discussion on this topic here (meaning the other thread).

Viewing 13 posts - 1 through 12 (of 12 total)

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