Post reply

querying the date field in sql2000: Urgent

  • December 11, 2003 at 5:51 am

    #160472

    I am doing an Assignment for my MSc Database design module. I am stuck with date query in sql server2000. I want to retrive rows assoicated with perticular date. I wrote a query:

    select * from Clinic where Date like '%09/12/2003%' its not working. please inform me any mistakes


    Sql server2000

  • December 11, 2003 at 7:13 am

    #485706

    There are many ways to filter (WHERE) on dates. The best way for you depends alot on the data, indexes and exactly what you want to query.

     

     

    Select * from Clinic 

    Where DateDiff(dd, [Date], '9/12/2003') = 0 

     

    or 

     

    -- If you want to take advantage of an index on your [Date] column... 

    Select * from Clinic 

    Where [Date] between '9/12/2003' and DateAdd (ss, -1, '9/13/2003')



    Once you understand the BITs, all the pieces come together

  • December 11, 2003 at 11:36 pm

    #485875

    Try this

    select * from Clinic where convert(char, Date, 112) = '20030912'

  • December 14, 2003 at 9:34 am

    #486073

    Thanks for the reply.


    Sql server2000

  • December 15, 2003 at 9:58 pm

    #486303

    SELECT *

    FROM Clinic

    WHERE Date BETWEEN '20031209' and '20031210'

    ADICOHN, r u sure that this will always return only the data for 09 Dec 2003 ???

    If it is true, my querry which is taking 2 minutes to execute will take only 12 secs. I am always using convert(char, DATE, 112) before between in my querries to avoid the confusion of hours, minuts and secs.

  • December 16, 2003 at 3:41 am

    #486343

    Thank you AdiCohn. It is very helpful.

Viewing 6 posts - 1 through 5 (of 5 total)

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