Simple date range in sql server 2005

Posted on 2009-12-29
Last Modified: 2012-05-08
I have the following query:

select transassgndate from remarketing
where transassgndate >= '10/1/2009' and transassgndate <= '11/5/2009'

Why does this query return rows with dates in the year 2008?
Question by:ipaman
    LVL 81

    Expert Comment


    The date format is not correct.
    Could you confirm : MM/DD/YYYY


    Try :

    select transassgndate from remarketing where transassgndate >= '01/10/2009' and transassgndate <= '05/11/2009'

    Open in new window


    Author Comment

    yes, for some reason the column type is a varchar. should be a datetime type.

    How can I compare dates (like I am trying to do above) if the column type is varchar?
    LVL 41

    Accepted Solution


    select transassgndate from remarketing
    where transassgndate BETWEEN convert(datetime,  '10/1/2009', 101) and convert(datetime, '11/5/2009', 101)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now