• 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).
0
vbnewbie01
Asked:
vbnewbie01
  • 2
  • 2
1 Solution
 
nmcdermaidCommented:
Convert the datetime to a date:

CONVERT(DATETIME,CONVERT(VARCHAR(10),<YourDateField>,120),120)


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.
0
 
vbnewbie01Author Commented:
great trick ... that seemed to work.  can you explain what the 120 is signifies?

thanks.
0
 
nmcdermaidCommented:
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:

CONVERT(VARCHAR(100),<YourDateField>,100)

CONVERT(VARCHAR(100),<YourDateField>,101)

CONVERT(VARCHAR(100),<YourDateField>,102)

CONVERT(VARCHAR(100),<YourDateField>,103)



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

thanks, nmcdermaid.
0

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