Filter on the date part of a SQL datetime field

Posted on 2006-04-11
Last Modified: 2008-03-10

I have a VB.NET application which works with a SQL database. In the SQL I have a table which contains 2 datetime values.

In VB I want my application to show all records with a datetime value in a certain filter. The probem is that the filter in VB is a date filter.

My question is how can I check on the date part of a datetime column in SQL. The time can be whatever it is, as long as the date is correct.

My problem:
in Vb the filter is 01/01/01 00:00:00
In SQL the FromDateTimefield is 01/01/01 13:00:00 and the ToDateTimeField is 01/01/01 14:00:00

sqlCmd = New SqlCommand("SELECT * FROM Table WHERE @d BETWEEN FromDateTime AND ToDateTime", objSQLConn)
sqlCmd.Parameters.Add("@d", datDate)
=> @d is not between the fromdatetime and the todatetime because of the 00:00:00 part.

Tnx for the help!
Question by:tsay
    LVL 142

    Accepted Solution

    sqlCmd = New SqlCommand("SELECT * FROM Table WHERE @d between convert( datetime, convert( varchar(10), FromDateTime , 120) , 120) AND dateadd(day, 1, convert ( datetime, convert( varchar(10), ToDateTime, 120), 120)) ", objSQLConn)
    LVL 4

    Expert Comment

    try adding
    sqlCmd.Parameters(2).DbType = DbType.Date
    LVL 5

    Author Comment

    Perfect angelIII, tnx!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Suggested Solutions

    Title # Comments Views Activity
    Gridview boundfield question 4 42
    How to do in LINQ?? 18 45
    Array Median As Double 4 15
    How to trouble shoot .net contact form 3 21
    I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
    Introduction When many people think of the WebBrowser ( control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    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…

    760 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

    13 Experts available now in Live!

    Get 1:1 Help Now