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?
  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
  3. satya Moderator

  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
  5. dineshasanka Moderator

    You can use datediff function as well datediff(d,Col1,'1/1/2007') = 0
  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]

Share This Page