Solved

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

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need help with a query 14 54
.NET Redemption/Outlook Examples 3 34
ASP.NET VB code for check and insert in a table 4 45
Javascript function 3 22
A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

751 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