search datetime field

How can I search a datetime field with MSSQL 2008 / ASP.NET VB?

Here is a sample record:

9/29/1971 12:15 AM (This is a DateTime Field)

when I use the following:

select * from my table where MyDateTime LIKE '9/29/1971'

I get nothing?

Also while I am at it how would I search for time as well?

select * from my table where MyDateTime LIKE '12:15 AM'

Thanks!

Try

Code:

---

select * from my table where MyDateTime LIKE '%9/29/1971%'

---

Nope still nothing...

Don't you get an error?

I would expect one when you try to use the Like operator with a DateTime field (which can be corrected by using Convert to make it a Char instead).

Dates can not be seached using a like command. If you want all records from a certian date ('9/29/1971') then

Select * from tableName where MyDateTime Between '9/29/1971' AND '9/30/1971'

If you want to use a time as the only match (any record from any date that happened at '12:15 AM' then you need to look at CONVERT and look at the DateType conversions.

Would you mind showing me an example Si, please?

I am sure I will get yelled at for this, but here is what I did to make it work. Please note I was getting the value from a textbox which is why I added the second item.

Code:

---

Dim ReportDate As Date
                Dim ReportDatePlus As Date
                ReportDate = txtReportDate.Text
                ReportDatePlus = ReportDate.AddDays(1)
                condition2 = "ReportDateTime between '" + ReportDate + "' AND '" + ReportDatePlus + "'"

---

To find dates using Like, without using Convert, make sure the criteria is based on the format mon dd yyyy hh:mi:ss:mmm which is the default (and equivalent to Convert date style 9 or 109)

MyDateTime Like 'Sep 29 1971%'
MyDateTime Like '%12:15%'

Using AddDays is better than my suggestion (or any use of Like) in terms of speed, because you only need to alter one parameter in a quick way (it adds one to a number), rather than the value in every row of the table in a slower way (convert a number to multiple string parts and join them). I suspect the comparison part is faster too.


However there are two issues with your code which could cause problems, both of which are unrelated to this change. They are due to issues with date formats, which means today (Oct 5th) could be interpreted as May 10th.

The first is the use of a Textbox to input a Date, which means an unreliable conversion needs to be done. It would be safer (and almost certainly better for the user) if you used a DateTimePicker instead.

The other is the way you append the date values to the SQL, which also uses an unreliable method. Ideally you should use Parameters (not just for dates, but any kind of values), but you could 'just' format the values as shown in How do I use values (numbers, strings, dates) in SQL statements? (from our Database Development FAQs/Tutorials at the top of this forum)[/i].