Solved

sql 2005 where clause with smalldatetime

Posted on 2015-02-05
4
314 Views
Last Modified: 2015-02-06
I have a sql 2005 database that has a smalldatetime field.  I am creating an asp.net application with vb.net code behind that will return a dataset from that table and fill a grid.  On my form there is a data picker.  When you select a date from the date picker it should return all records from the table that have the selected date.  The date picker returns a date in this format..
mm/dd/yyyy  here is the query I am working on but cant figure out how to get it to work... This is one of many different way I have tried...any help would be great - Todays_Date is the field with smalldatetime data type - in the example below I am just plugging in any date that is formatted how the date picker would

SELECT     KEYField, GUID, FKEYEmployee, RadioEntity, FKEYRadio_Number, Todays_Date, TimeOut, TimeIn, Location, Note, EnteredBY, EnteryTime, Status, 
                      FKEYTract, Check_Out_By, Check_Out_Time, Check_In_By, Check_In_Time
FROM         [tblCheckIn-Out]
WHERE     (TO_CHAR(Todays_Date, 'MM/DD/YYYY') = '2 / 4 / 2015')

Open in new window

0
Comment
Question by:David Modugno
[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
4 Comments
 
LVL 12

Expert Comment

by:Habib Pourfard
ID: 40592933
The following query will work if you pass '02/04/2015' instead of '2 / 4 / 2015':

SELECT     KEYField, GUID, FKEYEmployee, RadioEntity, FKEYRadio_Number, Todays_Date, TimeOut, TimeIn, Location, Note, EnteredBY, EnteryTime, Status, 
                      FKEYTract, Check_Out_By, Check_Out_Time, Check_In_By, Check_In_Time
FROM         [tblCheckIn-Out]
WHERE     CONVERT(char(10), Todays_Date, 101) = '02/04/2015'

Open in new window

0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40592956
For best performance you should avoid applying functions on each row of data to achieve a filter. Plus it is better to avoid the data type conversion from smalldatetime as well.

So, Instead of moving each and every row of data into a varchar in the format mm/dd/yyyy,
alter your single parameter into 2 smalldatetime values like this:

WHERE (
                [Todays_Date] >= convert(smalldatetime, @your_param, 101)
        AND
                [Todays_Date] < DATEADD(day,1, convert(smalldatetime, @your_param, 101) )
             )

This is known as using SARGable predicates.

The SQL may look more complex, but it will be faster.

Note: Please do NOT use "between", see: "Beware of Between"
0
 

Author Closing Comment

by:David Modugno
ID: 40594176
Thanks for the help.. it worked perfectly
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40594880
Excellent! Thanks for the feedback. Cheers, Paul
0

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

726 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