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?
 
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
 
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.