SELECT DISTINCT from date field by month

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)

SELECT DISTINCT from date field by month

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-01-06 : 08:28:16

Eleonora writes "I have this script

Dim rsMonth
set rsMonth=Server.CreateObject("ADODB.Recordset")
Set rsMonth = Conn.execute("SELECT DISTINCT month(DateApplied)) FROM seekers")
While Not rsMonth.EOF

=month(rsMonth("DateApplied"))

rsMonth.MoveNext
Wend

I want this script to brakedown my records by month

It works but when I try to display the data with "=month(rsMonth("DateApplied"))" I get this error

ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name or ordinal. "

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-06 : 08:28:35

do SELECT DISTINCT month(DateApplied)) as DateApplied FROM seekers

Go with the flow & have fun! Else fight the flow

SqlStar
Posting Yak Master

121 Posts

Posted - 2005-01-06 : 08:31:03

Hi,

You have to give alias column name for this part of query "month(DateApplied))". It means your query should be like this:
SELECT DISTINCT month(DateApplied)) As Months FROM seekers

Then, this statement "=month(rsMonth("DateApplied"))" should be changed like this "=month(rsMonth("Months"))"

I hope your problem will solve.

:) While we stop to think, we often miss our opportunity :)

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-01-06 : 08:38:34

SqlStar
Whilst your solution is right, surely spirit1's answer is easier as all that needs to be added is AS DateApplied to the SQL string

Just thought i'd point that out in case it confuses the poster

Andy

SqlStar
Posting Yak Master

121 Posts

Posted - 2005-01-06 : 08:41:57

AndyB13,

I have not seen the spirit1's answer, while I was writing reply to the poster. I also agree with spirit1's answer.

:) While we stop to think, we often miss our opportunity :)

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-01-06 : 08:49:51

I know, i had gathered that.
My response was for the benefit of the poster to avoid the confusion of which answer was correct which both where
:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources