Solved

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

Posted on 2014-09-07
5
1,070 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
  • 2
  • 2
5 Comments
 
LVL 28

Expert Comment

by:becraig
Comment Utility
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
Comment Utility
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 28

Accepted Solution

by:
becraig earned 500 total points
Comment Utility
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
Comment Utility
Thanks for the links...very informative...it is working now!

jimbo99999
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now