asked on

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).

ASKER CERTIFIED SOLUTION

nmcdermaid

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

vbnewbie01

ASKER

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

thanks.

nmcdermaid

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.

vbnewbie01

ASKER

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

thanks, nmcdermaid.