Querying Months - 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

Transact-SQL (2000)

Querying Months

Author

Topic

moochie169
Starting Member

4 Posts

Posted - 2007-09-18 : 12:34:53

Hi,

I'm still in the process of learning to write SQL codes. I need to display all the months within the date range a user puts in. I actually got this part from a coworker. But if I run this code from 12/01/06 - 12/31/08, everything looks fine except that the result does not show Dec of 07 and Dec of 08. I would greatly appreciate it if someone can help me figure this out. I'm trying to meet a deadline. Thank you so much!

Declare @StartDate smalldatetime,
@EndDate smalldatetime set @StartDate = '12/01/2006'
set @EndDate = '12/31/2008'

SET Transaction Isolation Level Read Uncommitted

Declare @OutputTable table (theDate smalldatetime)

Declare @monthcount smallint
set @monthcount = DateDiff(m, @StartDate, @EndDate)

declare @i smallint
set @i = 0
while @i <= @monthcount
begin
set nocount on

insert into @outputtable
SELECT
(Case
when datepart(m, DateAdd(m, @i, @StartDate)) <= 11
then cast(DateAdd(d,-1, Cast(cast(datepart(m, DateAdd(m, @i, @StartDate)) + 1 as varchar(2)) + '/01/' + cast(datepart(yyyy, DateAdd(m, @i, @StartDate)) as varchar(4)) as datetime)) as smalldatetime)
else cast(DateAdd(d,-1, Cast('01/01/' + cast(datepart(yyyy, @StartDate) + 1 as varchar(4)) as datetime)) as smalldatetime)
end) as theMonth

set @i = @i + 1
end
--
set nocount off

select *
from @outputtable

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-18 : 12:55:46

Try <= 12 instead of <= 11

E 12°55'05.25"
N 56°04'39.16"

moochie169
Starting Member

4 Posts

Posted - 2007-09-18 : 13:02:31

I tried that earlier but this is what I got:

1 Row(s) affected
1 Row(s) affected
1 Row(s) affected
1 Row(s) affected
Error: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. (State:22008, Native Code: F2)
Error: The statement has been terminated. (State:01000, Native Code: E25)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-18 : 13:04:51

Which DBMS are you using?

E 12°55'05.25"
N 56°04'39.16"

moochie169
Starting Member

4 Posts

Posted - 2007-09-18 : 13:20:35

MS SQL Server2000

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-18 : 13:21:23

[code]-- prepare input parameters
Declare @StartDate smalldatetime,
@EndDate smalldatetime

select @StartDate = '12/01/2006',
@EndDate = '12/31/2008'

-- stage data
Declare @OutputTable table (theDate smalldatetime)

select @startdate = dateadd(month, datediff(month, -1, @startdate), -1),
@enddate = dateadd(month, datediff(month, -1, @enddate), -1)

-- do the work
while @startdate <= @enddate
begin
insert @outputtable
SELECT @startdate

set @startdate = dateadd(month, 1, @startdate)
end

-- show the dates
select thedate from @outputtable
order by thedate[/code]

E 12°55'05.25"
N 56°04'39.16"

moochie169
Starting Member

4 Posts

Posted - 2007-09-18 : 14:21:11

THANK YOU SO MUCH!!! This is very helpful.

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-09-20 : 16:50:44

I noticed in your example that all the dates you returned were EOM dates. I noticed that Peso's example didn't return EOM dates. I changed one line of his code to make it return EOM dates. There may be an easier (shorter) way to do what I did.

-- prepare input parameters
Declare @StartDate smalldatetime,
@EndDate smalldatetime

select @StartDate = '12/01/2006',
@EndDate = '12/31/2008'

-- stage data
Declare @OutputTable table (theDate smalldatetime)

select @startdate = dateadd(month, datediff(month, -1, @startdate), -1),
@enddate = dateadd(month, datediff(month, -1, @enddate), -1)

-- do the work
while @startdate <= @enddate
begin
insert @outputtable
SELECT @startdate

set @startdate = dateadd(day, -1, convert(datetime,convert(varchar(2),datepart(mm,dateadd(month, 2, @startdate)))+'/01/'+ convert(varchar(4),datepart(yyyy,dateadd(month, 2, @startdate)))))
end

-- show the dates
select thedate from @outputtable
order by thedate

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources