Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server ASP
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server ASP Discussions about ASP programming with Microsoft's SQL Server. For more ASP forums, see the ASP forum category.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server ASP section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old December 15th, 2008, 06:07 AM
Registered User
 
Join Date: Dec 2008
Posts: 2
Thanks: 2
Thanked 0 Times in 0 Posts
Default 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?..
Reply With Quote
  #2 (permalink)  
Old December 15th, 2008, 08:48 AM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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?
__________________
===============================================
Doug Parsons
Wrox online library: Wrox Books 24 x 7
Did someone here help you? Click on their post!
"Easy is the path to wisdom for those not blinded by themselves."
===============================================
Reply With Quote
The Following User Says Thank You to dparsons For This Useful Post:
milton.fernando (December 15th, 2008)
  #3 (permalink)  
Old December 15th, 2008, 09:33 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,652
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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.

Last edited by Old Pedant; December 15th, 2008 at 09:38 PM.
Reply With Quote
The Following User Says Thank You to Old Pedant For This Useful Post:
milton.fernando (December 15th, 2008)
  #4 (permalink)  
Old December 15th, 2008, 11:08 PM
Registered User
 
Join Date: Dec 2008
Posts: 2
Thanks: 2
Thanked 0 Times in 0 Posts
Default Thanks

this code was perfect for my problem..
Reply With Quote
  #5 (permalink)  
Old December 16th, 2008, 08:47 AM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

Quote:
Originally Posted by Old Pedant View Post

*** 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
__________________
===============================================
Doug Parsons
Wrox online library: Wrox Books 24 x 7
Did someone here help you? Click on their post!
"Easy is the path to wisdom for those not blinded by themselves."
===============================================
Reply With Quote
  #6 (permalink)  
Old December 17th, 2008, 09:27 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,652
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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.

Last edited by Old Pedant; December 17th, 2008 at 09:34 PM.
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Converting month name to month number kalyanykk SQL Server 2005 7 August 19th, 2008 10:37 PM
Select .. Top 10 by Month? crabjoe SQL Server 2000 17 March 27th, 2008 11:48 AM
Seek help for select Month in list box apple88 Classic ASP Databases 1 October 11th, 2007 01:11 PM
getting the second saturday of every month haleem .NET Framework 2.0 1 May 3rd, 2007 12:38 AM
query Current Month, Month+1, Month+2, Month+3 anterior Access 2 September 24th, 2006 08:25 PM



All times are GMT -4. The time now is 04:20 AM.


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