al4629740
asked on
search records based on string name of month compared to data format
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())
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())
ASKER
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?
How can I search by 1, 2, 3...12?
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:
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
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}
Then it's a matter of..
select * from tblOrgHours where Month(ActivityDate) = {the combo box value}
ASKER
Yes I will need the year also....thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>very bad practice which even has a technical term (which I forgot how is called)
the term SARGABLE is relevant here.
As chaau rightly pointed out the method in the question isn't "sargable"
the term SARGABLE is relevant here.
Rules of thumbhttp://en.wikipedia.org/wiki/Sargable
Avoid functions using table values in a sql condition.
Avoid non-sargable predicates and replace them with sargable equivalents.
As chaau rightly pointed out the method in the question isn't "sargable"
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: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 useOpen in new window
The DateAdd part looks complicated, but believe me this is much faster then using DATENAME(month, ActivityDate) = 'January'