Solved

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

Posted on 2014-09-07
5
1,083 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 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 500 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 48

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

920 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

14 Experts available now in Live!

Get 1:1 Help Now