Solved

search records based on string name of month compared to data format

Posted on 2014-07-22
7
196 Views
Last Modified: 2014-07-23
I have a database table in sql where I have attendance records.  The ActivityDate column has a datatype of date.

In my vb6 form I have a dropdown box that the user selects which month they are looking to query. ie. "January, February, March, etc..."

Once they select a month, how can I use the month name "January" to select all records that contain a January ActivityDate?

Would this be correct?

select * from tblOrgHours where ActivityDate = DATENAME(January, GETDATE())
0
Comment
Question by:al4629740
[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
7 Comments
 
LVL 25

Expert Comment

by:chaau
ID: 40213249
No, this will not be correct. The correct answer will be:
select * from tblOrgHours where DATENAME(month, ActivityDate) = 'January'

Open in new window

However, do not rush and use this code. I will explain you why. When you are using the functions against the columns the database engine is unable to use indexes unless you have an index defined to that function. It is very bad practice which even has a technical term (which I forgot how is called). The better syntax would be to use:
select * from tblOrgHours where ActivityDate => '20140101' and ActivityDate < '20140201' 
-- i.e. all dates from January 1, 2014 to January 31, 2014

Open in new window

First of all, let's find out what do you want to select as a "January". Do you want to select all records for every January of every year? Do you want to select records for January-2014 (or a user selected year)? I bet you will answer yes to the latter. In this case you need to construct your query accordingly. I recommend that in your application you define a list of values for your dropdown box, so that each of the months has an index, i.e. January=1, February=2, etc. Then you need to provide a dropdown for the year. When you have defined this you can use
Select  * from tblOrgHours where ActivityDate => DateAdd(yy, @Year-1900, DateAdd(m,  @Month - 1, 0))  And ActivityDate < DateAdd(yy, @Year-1900, DateAdd(m,  @Month, 0)) 

Open in new window

The DateAdd part looks complicated, but believe me this is much faster then using  DATENAME(month, ActivityDate) = 'January'
0
 

Author Comment

by:al4629740
ID: 40213270
What if I just simply use  January=1, February=2 and break off the string 1, 2 and then search by that?

How can I search by 1, 2, 3...12?
0
 
LVL 25

Expert Comment

by:chaau
ID: 40213283
What about the year? Don't you need it?
If you really do need to search records for the month regardless of the year, then use:
select * from tblOrgHours where DATEPART(month, ActivityDate) = 1

Open in new window

0
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.

 
LVL 66

Expert Comment

by:Jim Horn
ID: 40213334
The way I've regularly pull this off in SSRS, Excel VBA/PowerQuery, and Access is that the combo box is a set that has two columns, the int 1..12 of the month which is not visible to the users, and the January..December text that is visible.  User selects October, the value that the combo box passes is 10.

Then it's a matter of..
select * from tblOrgHours where Month(ActivityDate) = {the combo box value}
0
 

Author Comment

by:al4629740
ID: 40213336
Yes I will need the year also....thanks
0
 
LVL 25

Accepted Solution

by:
chaau earned 500 total points
ID: 40213349
So, then use the query I have provided
Select  * from tblOrgHours where ActivityDate => DateAdd(yy, @Year-1900, DateAdd(m,  @Month - 1, 0))  And ActivityDate < DateAdd(yy, @Year-1900, DateAdd(m,  @Month, 0))

Replace the @month and @year parameters with those from your form and you are good to go
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40213601
>>very bad practice which even has a technical term (which I forgot how is called)

the term SARGABLE is relevant here.

Rules of thumb

Avoid functions using table values in a sql condition.
Avoid non-sargable predicates and replace them with sargable equivalents.
http://en.wikipedia.org/wiki/Sargable

As chaau rightly pointed out the method in the question isn't "sargable"
0

Featured Post

Independent Software Vendors: 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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

734 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