VB.Net - Query Date Field Disregarding Time in DateTime Field

Hello Experts!

I am having some trouble with Select query.  I am trying to compare a date against a SQL table field that has DateTime.
I have values 3/19/2012 12:54:32 PM in the field but the User is just entering 3/19/2012 in the textbox.

How can I disregard the time and just match on the 3/19/2012?

Thanks for the help,
jimbo99999
Jimbo99999Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

becraigCommented:
I am assuming here that you are taking the value back from a html form field and performing the query against the DB

If so simply cast the input:
(SELECT CAST(FLOOR(CAST( 3/19/2012 AS float)) AS datetime))
0
Jimbo99999Author Commented:
Hello There becraig:

I apologize here I was not clear in my description.  This is just a VB.Net windows app and I am just grabbing the User input from a textbox.
0
becraigCommented:
Ok so you still have to change this in your code.

Either converting the string from the textbox to date-time
http://www.dotnetperls.com/datetime-parse-vbnet

or convert on the sql side the actual datetime from the db to date part
http://sqlhints.com/2013/07/14/how-to-get-date-part-only-from-datetime-in-sql-server/
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jimbo99999Author Commented:
Thanks for the links...very informative...it is working now!

jimbo99999
0
PortletPaulfreelancerCommented:
sorry for lateness - I was called away - but hope you don't mind the following

there is a complete alternative approach: do not change the data, change the method of filtering

http://en.wikipedia.org/wiki/Sargable

Rules of thumb
Avoid applying functions on data values in a sql condition.
Avoid non-sargable predicates and replace them with sargable equivalents.

>>"I have values 3/19/2012 12:54:32 PM in the field but the User is just entering 3/19/2012"
in your case, there is a single date (but many rows of data)

where that_field >= '20120319' and that_field < dateadd(day,1,'20120319')

and you get the results you need, but avoid lots of unnecessary calculations on the data and ensure the query can access indexes on that_field too

---------
if you do have to change the data, the second reference above gives some inefficient methods, these are generally the best:
SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) -- returns datetime
SELECT CONVERT (DATE, GETDATE()) -- returns date

, or the equivalent using cast:
SELECT CAST (GETDATE() AS DATE) -- returns date
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

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.