search for date without time

Posted on 2005-05-02
Last Modified: 2010-03-19
hi there;

i have a bit of a date issue.

using (vb) when i create a new record, i insert the date into the sql server database.

it is inserted in the datetime format (i.e. 5/2/2005 1:14:10 AM)

the issue is, when i do a seach for all records created on a specific date (for example, 5/2/2005), nothing is displayed because it, by default, is not just looking to match the date but also to match the exact time as well.

my question is, how do i do a seach for all records created on a specific date regardless of the time they were created (i.e. a search for 5/2/2005 will return all records created on that day).
Question by:vbnewbie01
    LVL 30

    Accepted Solution

    Convert the datetime to a date:


    This expression first truncates the time by converting it to a VARCHAR(10) in this format: yyy-mm-dd
    Then converts it back to a date.

    So it would be something like this:

    WHERE CONVERT(DATETIME,CONVERT(VARCHAR(10),<YourDateField>,120),120) = '2003-02-05'

    Thats how we do it in Australia. If you're in America you might want to swap the day and month.
    LVL 1

    Author Comment

    great trick ... that seemed to work.  can you explain what the 120 is signifies?

    LVL 30

    Expert Comment

    I don't know if you have books online for SQL server but if you look up CONVERT in there it will all become clear.

    When you convert a datetime to (or from) something you need to tell it what format it should be converted to.

    120 means this format: yyyy-mm-dd hh:mi:ss

    then when you only take the first ten characeters ( the VARCHAR(10) )it looks like this: yyyy-mm-dd

    try these and see how they come out:





    They're all just different formats.

    In fact you could use any of these numbers, as long as its the same number in both convert functions.
    LVL 1

    Author Comment

    ah ... of course.  it's all coming back to me now :)

    thanks, nmcdermaid.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    The steps for moving the system databases to a new location are documented in the following technical article: However sometimes after the moving process is finished, though SQL i…
    Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    794 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

    18 Experts available now in Live!

    Get 1:1 Help Now