select month from database table

Select MONTH(datecolumn) will return the MONTH number from the dateColumn
---
Shannon Lowder
Database Engineer
toyboxcreations.net

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

read chapmandew's comments again.  :)

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 & "'"

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.

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.  

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

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 & "'"

Select allOpen in new window

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 & "'"

Select allOpen in new window

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.

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.

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.

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

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.
 

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

ok thanks for everyones help i will take everyones comment into consideration.

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.

Thanks this worked with some tweeking your awesome

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

>>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. :)