Solved

sql 2005 where clause with smalldatetime

Posted on 2015-02-05
4
297 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
  • 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
always on switch back after failover 2 35
sql server tables from access 18 22
Unable to Uninstall Visual Studio 2015 7 27
Sort GridView by ID Descending 1 13
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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.
Viewers will learn how the fundamental information of how to create a table.

832 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