Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-09-07
5
Medium Priority
?
1,145 Views
Last Modified: 2014-09-07
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
0
Comment
Question by:Jimbo99999
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 29

Expert Comment

by:becraig
ID: 40309041
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
 

Author Comment

by:Jimbo99999
ID: 40309053
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
 
LVL 29

Accepted Solution

by:
becraig earned 2000 total points
ID: 40309071
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
 

Author Comment

by:Jimbo99999
ID: 40309100
Thanks for the links...very informative...it is working now!

jimbo99999
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40309126
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question