p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   SQL Server ASP (http://p2p.wrox.com/forumdisplay.php?f=101)
-   -   how to select from Month (http://p2p.wrox.com/showthread.php?t=71813)

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

how to select from Month
 
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

Thanks
 
this code was perfect for my problem..

dparsons December 16th, 2008 09:47 AM

Quote:

Originally Posted by Old Pedant (Post 231618)

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


All times are GMT -4. The time now is 09:33 AM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.