Get DISTINCT DateTime 1

[A1Pat]

Hi all,

I'm trying to find a way that list DISTINCT Year(date) from a database simpliest.

Here is how I came up:

'Get Years for Monthly Report
sql = "SELECT DateTime FROM tblTechSupports ORDER BY DateTime"
set rs = objConn.Execute(sql)
x = ""
if not rs.eof then
	arrYears = rs.GetRows()
end if
If isArray(arrYears) Then	
  For i=0 to ubound(arrYears,2)
    if Year(arrYears(0,i)) <> x then
	x = Year(arrYears(0,i))
	response.write arrYears(0,i) & "<BR>"
    end if
  Next
End If

Is there a way to make it less messy?
Thanks!

 

[cLFlaVA]

this is a question for the SQL Server forum (forum183). take a look into the DISTINCT keyword, and the DATEPART function.



*cLFlaVA
----------------------------
[tt]"quote goes here"[/tt]
[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[gmmastros]

What database?

If this is SQL Server, then you could get the DB to do the work for you.

Select Distinct Year(DateTime) As [Year]
From   tblTechSupports
Order By Year(DateTime)

I know this would work for SQL Server. If you are using another database engine, there's probably still a way to return just the data you want.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[monksnake]

Call me crazy, but what about this:

'Get Years for Monthly Report
sql = "SELECT [!]DISTINCT[/!] DateTime FROM tblTechSupports ORDER BY DateTime"
set rs = objConn.Execute(sql)
if rs.RecordCount then
    arrYears = rs.GetRows()
    For i in arrYears
       response.write arrYears(0,i) & "<BR>"
    Next
End If

<.

 

[monksnake]

Oh I jumped the gun, DISTINCT YEARRRRRRRR

<.

 

[Sheco]

Another way: GROUP BY

 

[A1Pat]

I agree with all of you that the question somehow should be in the SQL forum... however, I do have my reason to post it here (maybe at first).

I did try this:

"SELECT DISTINCT YEAR(DateTime) FROM tblTechSupports ORDER BY DateTime"

But I got an error saying the query is not recognized or something like that. I also tried "DISTINCT DateTime" and I got all the data that has the same year anyway... and that's why I believe this forum is more fit for my question than the SQL forum. But if you can help me do so in the SQL query, it would be terrific!!!
Thanks!

 

[gmmastros]

"SELECT DISTINCT YEAR(DateTime) [!]As Year[/!] FROM tblTechSupports ORDER BY [!]Year([/!]DateTime[!])[/!]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Oh... You still didn't tell us what the database is. Is it Access, SQL Server, MySQL, etc...?


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[A1Pat]

I'm sorry... it's SQL Sever db.

 

[A1Pat]

AHA!!! George, you did it for me again!
Your SQL query is perfect for my intention.

Thanks again!

 

[monksnake]

I think that was the hardest query George has ever done. Cereal.

<.