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
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
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the links...very informative...it is working now!
jimbo99999
jimbo99999
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
>>"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:
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.
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
SELECT CONVERT (DATE, GETDATE()) -- returns date
, or the equivalent using cast:
SELECT CAST (GETDATE() AS DATE) -- returns date
If so simply cast the input:
(SELECT CAST(FLOOR(CAST( 3/19/2012 AS float)) AS datetime))