• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 432
  • Last Modified:

VB.Net - Compare SQL DateField Ignoring Time

Good Day Experts!

I have a bit of an issue that I am trying to work through with regards to a DateTime field in my SQL table.  It contains the time after the date.  

I give the User a Start Date and and End Date to enter on the UI.  I want to retrieve records in the SQL table based on a date field.  So, I am "asking" in the SQL query for the date field to be >= StartDate and <= End Date.  

How do I get the query to look at the date field without respect to the time on the back of the date?

Thanks for helping,
jimbo99999
0
Jimbo99999
Asked:
Jimbo99999
2 Solutions
 
ChetOS82Commented:
CONVERT (DATE, DATE_FIELD)  >= StartDate AND CONVERT(DATE, DATE_FIELD) <= EndDate
0
 
Jacques Bourgeois (James Burger)PresidentCommented:
And maybe, the first thing to do, if you do not need them, would be not to store the time in the database.

Too many programmers use Date.Now when recording the current date, which always saves the time along the date and ends up in most situations creating problems such as yours.

Date.Today is the expression to use when you do not need the time.
0
 
Jimbo99999Author Commented:
Hey James:

Unfortunately I am working with data that is already in the tables.  But, I understand your sentiments with not saving the time.

Thanks,
jimbo99999
0
 
Jimbo99999Author Commented:
Chet:

Thanks for responding. I will try it out in the morning when I get to work.

Thanks,
jimbo99999
0
 
ArkCommented:
Why not using query with parameters?
 'Assuming startDate/endDate were entered from UI
startDate = startDate.Date 'get date part only
endDate = endDate.AddDays(1).Date 'get date part for next day
Dim dt As New DataTable
Using da As New SqlDataAdapter("SELECT * FROM YourTable WHERE StartDate>=@StartDate AND EndDate<@EndDate", yourConnectionString)
    da.SelectCommand.Parameters.AddWithValue("@StartDate", startDate)
    da.SelectCommand.Parameters.AddWithValue("@EndDate", endDate)
    da.Fill(dt)
End Using

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now