SQL Server Performance

DateTime Field query...

Discussion in 'T-SQL Performance Tuning for Developers' started by alexjamesbrown, Sep 24, 2007.

  1. alexjamesbrown New Member

    I have upsized an Access database to SQL 2000.
    I am now in the process of sorting out all my SQL Queries so that they will work on SQL 2000.... one im having particular problem with is:
    SELECT * FROM Diary, DropDownDiaryCategory, DropDownDiaryInOut
    WHERE Diary.DiaryInOut = DropDownDiaryInOut.DDDiaryInOutID
    AND Diary.DiaryCategory = DropDownDiaryCategory.DiaryCategoryID
    AND DiaryType = 1
    AND DiaryDate = 17/09/2007
    ORDER BY DiaryTime ASC, DiaryNote ASC
    This works fine in ms access, HOWEVER it does not work in SQL 2000..
    any ideas whats going on?
    alexjamesbrown, Sep 24, 2007
    #2

  2. ndinakar Member

    I would change the way you are joining the tables to the standard ANSI Format. The datatype in SQL has to be in quotes.SELECT
    * FROM
    Diary DJOIN
    DropDownDiaryInOut DDI ON D.DiaryInOut = DDI.DDDiaryInOutIDJOIN
    DropDownDiaryCategory DDC ON D.DiaryCategory = DDC.DiaryCategoryIDWHERE
    DiaryType = 1
    AND DiaryDate = '20070917'ORDER BY DiaryTime
    ASC, DiaryNote ASC
    ndinakar, Sep 24, 2007
    #3

  3. satya Moderator

    satya, Sep 25, 2007
    #4

  4. Madhivanan Moderator

    It should be noted that you should always express date values in YYYYMMDD HH:MM:SS format to avoid conflict with local date settings
    Madhivanan, Sep 25, 2007
    #5

  5. dineshasanka Moderator

    You can use datediff function as well datediff(d,Col1,'1/1/2007') = 0
    dineshasanka, Sep 26, 2007
    #6

  6. Madhivanan Moderator

    Except that it wont make use of index if it is defined over datetime column [:)]
    [quote user="dineshasanka"]
    You can use datediff function as well datediff(d,Col1,'1/1/2007') = 0[/quote]
    Madhivanan, Sep 27, 2007
    #7

Share This Page