all records in a given month - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

all records in a given month

Author

Topic

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2005-05-09 : 12:54:38

Hi,

I'm passing in a DateTime into my stored procedure, and the datetime has the current year,month, day.

I want to list all the records for a given month.
example: all rows for April 2005 or all rows for Dec 2004.

One option for me is to pass in Start and End dates from my applicatoin, but I was wondering if I could do this with by passing in one datetime parameter.

So:

SELECT *
FROM articles
WHERE datecreated = @thismonth

I'm guessing I have to split the datecreated apart by year and month and compare those?
please help!

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-05-09 : 13:00:14

SELECT *
FROM articles
WHERE Month(datecreated) = @thismonth

then use a numaric month for @thismonth

After thought

Get out of the habit of using Select *

List all your fields, Select * will come back to haunt you.

Jim
Users <> Logic

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2005-05-09 : 13:01:41

cool thanks.

I'm guessing it should be:

WHERE year(datecreated) = @Year AND month(datecreated) = @month

thanks!

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-09 : 13:43:51

AAAAAAAHHHHHHHHHHHHHHHH



USE Northwind
GO

-- [CTRL]+K

DECLARE @Year char(4), @Month char(2), @Date datetime
SELECT @Year = '1996', @Month = '7'
SELECT @Date = CONVERT(datetime,@Year+'-'+@Month+'-01')

SELECT OrderDate FROM Orders 
 WHERE MONTH(OrderDate) = @Month AND YEAR(OrderDate) = @Year

SELECT OrderDate FROM Orders 
 WHERE OrderDate > = @Date AND OrderDate < DATEADD(mm,1,@Date)

Brett

8-)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-05-09 : 13:56:54

Doing it this way eliminates the need to use a date function on each row in the table, and would let it use an index on column datecreated, if it exists.

select
	*
from
	articles
where
	-- datecreated greater than or equal to  
	-- start of month passed in @thismonth
	datecreated >= dateadd(mm,datediff(mm,0,@thismonth),0)	and
	-- datecreated before the start of next month after
	-- start of month passed in @thismonth
	datecreated <  dateadd(mm,datediff(mm,0,@thismonth)+1,0)

CODO ERGO SUM

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-09 : 14:05:56

Yeah..you get an index seek...and no results


SELECT OrderDate FROM Orders
 WHERE OrderDate >= DATEADD(mm,datediff(mm,0,@m),0)
   AND OrderDate <  DATEADD(mm,datediff(mm,0,@m)+1,0)

I don't get it....

Brett

8-)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-05-09 : 14:10:52

Not sure what you used for a date, but I got this:

use Northwind
go

declare @m	datetime
select @m 	= '1998-04-22'


SELECT OrderDate FROM Orders
 WHERE OrderDate >= DATEADD(mm,datediff(mm,0,@m),0)
   AND OrderDate <  DATEADD(mm,datediff(mm,0,@m)+1,0)


OrderDate                                              
------------------------------------------------------ 
1998-04-01 00:00:00.000
1998-04-01 00:00:00.000
1998-04-01 00:00:00.000
1998-04-01 00:00:00.000
1998-04-02 00:00:00.000
1998-04-02 00:00:00.000
1998-04-02 00:00:00.000
1998-04-03 00:00:00.000
1998-04-03 00:00:00.000
1998-04-03 00:00:00.000
1998-04-06 00:00:00.000
1998-04-06 00:00:00.000
1998-04-06 00:00:00.000
1998-04-06 00:00:00.000
1998-04-07 00:00:00.000
1998-04-07 00:00:00.000
1998-04-07 00:00:00.000
1998-04-08 00:00:00.000
1998-04-08 00:00:00.000
1998-04-08 00:00:00.000
1998-04-09 00:00:00.000
1998-04-09 00:00:00.000
1998-04-09 00:00:00.000
1998-04-09 00:00:00.000
1998-04-10 00:00:00.000
1998-04-10 00:00:00.000
1998-04-10 00:00:00.000
1998-04-13 00:00:00.000
1998-04-13 00:00:00.000
1998-04-13 00:00:00.000
1998-04-14 00:00:00.000
1998-04-14 00:00:00.000
1998-04-14 00:00:00.000
1998-04-14 00:00:00.000
1998-04-15 00:00:00.000
1998-04-15 00:00:00.000
1998-04-15 00:00:00.000
1998-04-16 00:00:00.000
1998-04-16 00:00:00.000
1998-04-16 00:00:00.000
1998-04-17 00:00:00.000
1998-04-17 00:00:00.000
1998-04-17 00:00:00.000
1998-04-17 00:00:00.000
1998-04-20 00:00:00.000
1998-04-20 00:00:00.000
1998-04-20 00:00:00.000
1998-04-21 00:00:00.000
1998-04-21 00:00:00.000
1998-04-21 00:00:00.000
1998-04-22 00:00:00.000
1998-04-22 00:00:00.000
1998-04-22 00:00:00.000
1998-04-22 00:00:00.000
1998-04-23 00:00:00.000
1998-04-23 00:00:00.000
1998-04-23 00:00:00.000
1998-04-24 00:00:00.000
1998-04-24 00:00:00.000
1998-04-24 00:00:00.000
1998-04-27 00:00:00.000
1998-04-27 00:00:00.000
1998-04-27 00:00:00.000
1998-04-27 00:00:00.000
1998-04-28 00:00:00.000
1998-04-28 00:00:00.000
1998-04-28 00:00:00.000
1998-04-29 00:00:00.000
1998-04-29 00:00:00.000
1998-04-29 00:00:00.000
1998-04-30 00:00:00.000
1998-04-30 00:00:00.000
1998-04-30 00:00:00.000
1998-04-30 00:00:00.000

(74 row(s) affected)

CODO ERGO SUM

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-09 : 14:16:30

Oh, So @ThisMonth is the month number as he suggested....it's an actual date....

Gotcha

Either way

Brett

8-)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-05-09 : 14:22:01

Yes, he said he wanted to pass a single datetime parameter, so I was assuming he would pass some date in the month that he wanted.

quote:
Originally posted by X002548

Oh, So @ThisMonth is the month number as he suggested....it's an actual date....

Gotcha

Either way

Brett

8-)

CODO ERGO SUM

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-09 : 14:26:09

I though he was just gonna pass just the month number....

In either case, we've spent more time on this than he has....

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources