how to select from Month

milton.fernando · December 15th, 2008, 07:07 AM

i want select the transaction which are done in the current month alone how to do it..
i think ther are methods to find by date and year but i am not sure how to do it using month, can anybody help me with this?..

dparsons · December 15th, 2008, 09:48 AM

Something like this would work:

sql Code:

SELECT * FROM [TABLE] WHERE Month(DateColumn) = Month(GetDate())

The obvious problem here is that this query will return all rows where the month of a transaction is equal to the current date's month and does not take into account year. So the above query might possibly return you rows that are several years old.

I typically favor something like this in this scenario:

sql Code:

SELECT * FROM [TABLE] WHERE DateColumn BETWEEN @Date1 AND @Date2

Does this work for you?

Old Pedant · December 15th, 2008, 10:33 PM

But there is no reason you can't extend the first answer and do

Code:

SELECT * FROM tablename 
WHERE Year(DateColumn) = Year(getDate())
AND Month(DateColumn) = Month(getDate())

If the DateColumn is indexed, using BETWEEN and passing in @Date1 and @Date2 will be somewhat faster. But if the field is not indexed it probably won't make any noticable difference.

*** CAUTION! ***
If DateColumn actually contains both date and time, then doing

Code:

...WHERE DateColumn BETWEEN '1/1/2009' AND '1/31/2009'

will *NOT* give the right answer! You will end up omitting all records from 1/31/2009 except any that happen to have a time of midnight. If you DateColumn does include times, then you are better off doing

Code:

...WHERE DateColumn >= '1/1/2009' AND DateColumn < '2/1/2009'

Notice that we use >= for the start date but only use < for the end, thus ensuring we get all records from 1/31/2009 no matter what time they have.

milton.fernando · December 16th, 2008, 12:08 AM

this code was perfect for my problem..

dparsons · December 16th, 2008, 09:47 AM

Quote:

Originally Posted by Old Pedant

*** CAUTION! ***
If DateColumn actually contains both date and time, then doing

Code:

...WHERE DateColumn BETWEEN '1/1/2009' AND '1/31/2009'

will *NOT* give the right answer!

The above statement is only relevant to SQL2K8 or another RDBMS such as MySQL since SQL Server's standard way of storing Date and Time data is in a single DateTime field.

-Doug

Old Pedant · December 17th, 2008, 10:27 PM

What DB's is it *NOT* true in, Doug? And are you saying there are versions of SQL Server where it is not true?

I don't see how a DB could be ANSI-compliant and not have that situation exist. No matter how the DATETIME value is stored. (Of course, if the *USER* chooses to store the date and time in separate fields, that's a completely different issue/question/solution. But doing that creates its own complexities and I usually recommend against it.)

Anyway, all I wrote is also true in Access (or any JET-accessed DB or pseudo-db, such as Excel or text files).

And of course another way to solve the problem is to convert the date-and-time value to date-only as part of the BETWEEN:

Code:

Access/JET:
... WHERE DATEVALUE(DateColumn) BETWEEN '1/1/2009' AND '1/31/2009'
MySQL:
... WHERE DATE(DateColumn) BETWEEN '1/1/2009' AND '1/31/2009'
SQL Server:
... WHERE CONVERT(DATETIME, CONVERT(VARCHAR, DateColumn, 112), 112) BETWEEN '1/1/2009' AND '1/31/2009'

Only SQL Server is too stupid to give us a nice handy function to do this (and similarly omits other handy datetime functions).

Anyway...You do *NOT* want to use a function like that if the field is indexed, because you just caused a full scan of all values in the index. If you use the >= and < solution that I showed, you can take full advantage of an index on the column.