select month from database table

Hello
i have a date field in my MSSQL database and i have a vb program that i need to select a month from my table..
EXMAPLE
3/27/2010 4:42:07 PM

i want to select all fielsd that are in month (3)
LVL 1
Chris JonesLead Application Web DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chapmandewCommented:
select * from tablename
where fieldname >= '3/1/2010' and fieldname < '4/1/2010'

you're going to have someone on here tell you to do this:

select * from tablename
where month(fieldname) = 3

don't do it...its slow because it will need to scan the table performing that function on each record.  don't do it.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Shannon_LowderCommented:
Select MONTH(datecolumn) will return the MONTH number from the dateColumn
---
Shannon Lowder
Database Engineer
toyboxcreations.net
0
Shannon_LowderCommented:
To use this in a where clause, chapmandew is correct, use MONTH(dateColumn) = 3.  I usually use AND YEAR(datecolumn) = YEAR(getdate()) when I use this.
---
Shannon Lowder
Database Engineer
0
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

chapmandewCommented:
read chapmandew's comments again.  :)
0
Chris JonesLead Application Web DeveloperAuthor Commented:
hELLO

THIS IS MY SELECT STATEMENT I FIGURED I COULD POST IT I WILL MAKE SOME CGANGES AND GET BACK

        sqlstmt = "SELECT * FROM JobHistorySummary jhs LEFT JOIN JobHistoryDetail jhd ON jhs.JobHistoryID = jhd.JobHistoryID INNER JOIN JobHistoryDetailInfo jhi ON jhd.JobHistoryDetailID = jhi.JobHistoryDetailID WHERE dateadd(dd, datediff(dd, 0, jhs.EndTime), 0) = '" & find & "' AND jhs.MediaSetName= '" & jobtype & "'"
0
Neil RussellTechnical Development LeadCommented:
BUT.... "where fieldname >= '3/1/2010' and fieldname < '4/1/2010'"
chapmanrew Assumes you ONLY want month 3 from the current year. This might be correct but if you want anything in ANY month 3 it gets more complicated.
0
chapmandewCommented:
correct...assumptions must be made when the author doesn't specify.  However, I've been doing this long enough to have a pretty decent idea as to what the users are asking for.  
0
Shannon_LowderCommented:
Exactly.  The author would need to clearly define if he or she wants just the current month (of the current year), or if he or she wants the information for every March.

---
Shannon Lowder
Database Engineer
0
Chris JonesLead Application Web DeveloperAuthor Commented:
Hello just to clear up the month thing i get the month in my vb app but i tried a few things but get incorect syntax near =

CODE
        sqlstmt = "SELECT * FROM JobHistorySummary jhs LEFT JOIN JobHistoryDetail jhd ON jhs.JobHistoryID = jhd.JobHistoryID INNER JOIN JobHistoryDetailInfo jhi ON jhd.JobHistoryDetailID = jhi.JobHistoryDetailID WHERE dateadd(dd, datediff(dd, 0, MONTH(jhs.EndTime))= '" & getmonth & "', 0) = '" & find & "' AND jhs.MediaSetName= '" & jobtype & "'"

Open in new window

0
Chris JonesLead Application Web DeveloperAuthor Commented:
i fixed it here is my solution


        sqlstmt = "SELECT * FROM JobHistorySummary jhs LEFT JOIN JobHistoryDetail jhd ON jhs.JobHistoryID = jhd.JobHistoryID INNER JOIN JobHistoryDetailInfo jhi ON jhd.JobHistoryDetailID = jhi.JobHistoryDetailID WHERE  MONTH(jhs.EndTime)= '" & getmonth & "' AND jhs.MediaSetName= '" & jobtype & "'"

Open in new window

0
UnifiedISCommented:
Is getmonth your month number variable?

WHERE MONTH(jhs.EndTime)= " & getmonth & " AND jhs.MediaSetName= '" & jobtype & "'"

When building your SQL don't wrap numbers in single quotes, that is for string values
MONTH(date) returns an int

If you want to follow Chapmandews advice against using the MONTH function, you'll need to build your where clause like this:

DIM StartDate as datetime
DIM EndDate as datetime
StartDate = new datetime(YEAR(Now), getmonth, 1)
EndDate = new datetime(YEAR(DATEADD(mm, 1, StartDate)), MONTH(DATEADD(mm, 1, StartDate)), 1)

WHERE jhs.EndTime >= '" & StartDate & "' AND jhs.EndTime < '" & EndDate & "'"

Single quotes are needed here because you have a date.

0
Anthony PerkinsCommented:
blingtec903,

Did you actually read the comments posted about not using WHERE  MONTH(jhs.EndTime) ?  If so did you understand them? If you did do you not care about performance?  The reason we point this out repeatedly is because we have too many question posted here on this same subject, usually from developers that have just migrated from MS Access.
0
UnifiedISCommented:
Let's not confuse using something other than the best to mean that one doesn't care about performance.  Many database applications will never have an issue because the MONTH function is used instead of real date values.  I'm glad it is mentioned but you don't need to be "chicken little" about it.
0
Shannon_LowderCommented:
Are there any articles out there comparing the performance of MONTH(x) in the WHERE clause versus using a BETWEEN date range?  I think that would be useful information for users learning to manipulate dates in SQL.
---
Shannon Lowder
Database Engineer
0
Neil RussellTechnical Development LeadCommented:
Also, and yes i know it breaks more rules......, Consider using an INSERT/UPDATE/DELETE Triger to add you PK and Month number only into a new table. This will GREATLY enhance performance when it comes to extracting data based on your month number as there will be zero calculations involved purely index access.
 
0
Anthony PerkinsCommented:
>>Let's not confuse using something other than the best to mean that one doesn't care about performance.<<
We are here to teach good SQL practices, so when someone ignores these and proceeds to write sloppy code all we can do is point out the problems.  What you are overlooking is that this page will be seen not only by the author who may only care about maintaining his wife's recipes, but by thousands of other users for many years to come who will not be so enlightened.

>> I'm glad it is mentioned but you don't need to be "chicken little" about it.<<
And yes, I beg to differ with you.
0
Chris JonesLead Application Web DeveloperAuthor Commented:
ok thanks for everyones help i will take everyones comment into consideration.
0
UnifiedISCommented:
Instead of saying there is a performance issue, why not link to a reputable source that says it is a performance issue and explains it?  Enlightenment comes from comprehension not blindly following the advice of an online DBA.


0
Chris JonesLead Application Web DeveloperAuthor Commented:
Thanks this worked with some tweeking your awesome
0
Anthony PerkinsCommented:
>>Instead of saying there is a performance issue, why not link to a reputable source that says it is a performance issue and explains it? <<
Did you not read the accepted solution?  Here it is again in case you missed it:
"its slow because it will need to scan the table performing that function on each record."

If you need any more explanation then I suggest you post your own question.
0
chapmandewCommented:
>>Instead of saying there is a performance issue, why not link to a reputable source that says it is a performance issue and explains it? <<

Because Anthony and I ARE the reputable source. :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.