Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2006
    Posts
    1

    Unanswered: SQL Server equivalent for DATE LIKE '%__/Apr/2006%' (was "Please Give Me A Hand...")

    I'm happyguy and this is the first time to post my problem. Thanks a lot

    here is a SQL query that I know how to write in Oracle, which is

    Select * from AttandanceRecord where date like '%__/Apr/2006%'

    but, now i'm developing my system with using Ms Sql Server 2000, so I don't know how to write a query with same output as above...

    Please, can somebody help me??

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There are several ways to reproduce the query you've posted and have it work in SQL Server, but all of those ways suffer from a number of problems. A minor re-write could yield:
    Code:
    Select *
       FROM AttandanceRecord
       WHERE  '1 Apr 2006' <= date
          AND date < '1 May 2006'
    ...which performs much better.

    -PatP

  3. #3
    Join Date
    Apr 2006
    Location
    System.Web.UI.WebControls.Home.BedRoom2
    Posts
    42

    Lightbulb Another cool way

    What Pat suggested is more secure, this is another way it can be done

    I have used this query against the Northwind db which is default with MSSQL:

    select * from employees where datepart(month,birthdate)=12 and datepart(year,birthdate)=1948

    Should get one record with name Nancy.
    BEWARE SQL has DATETIME datatype, both in one.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Using datepart() throws any indexes out the window.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Apr 2006
    Location
    System.Web.UI.WebControls.Home.BedRoom2
    Posts
    42
    What does that mean, please explain.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "throws indexes out the window" means the index (if any) on the birthdate column will be ignored, and the query will use a table scan instead of an index lookup as it would with pat's query (post #2)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2006
    Location
    System.Web.UI.WebControls.Home.BedRoom2
    Posts
    42
    That makes sense.

    Thanks r937

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •