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

search for date without time

hi there;

i have a bit of a date issue.

using asp.net (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).
  • 2
  • 2
1 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.
vbnewbie01Author Commented:
great trick ... that seemed to work.  can you explain what the 120 is signifies?

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.
vbnewbie01Author Commented:
ah ... of course.  it's all coming back to me now :)

thanks, nmcdermaid.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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