List dates in months from database

Hey, I need som help to list my added dates from database, and split it into their added month.

I have no clue on how to do it... Soe can someone please sh开发者_高级运维ow me examples, or maybe some tutorials how to do?

Thx

Something along the lines of this, perhaps?

SELECT * FROM table GROUP BY MONTH(dateColumn)

SELECT * FROM table WHERE MONTH(dateColumn) = 9

A must-read reference for date & time handling functions in MySQL is:

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

Are you looking for the MySQL MONTH() Function?

  Query: SELECT MONTH(NOW());
 Output: 11

Not entirely sure what you mean but here goes...

The sample below creates a test collection (assumably your collection retrieved from the database) and groups them by Month and Year and then displays the result. It uses Linq and anonymous objects which you could easily replace with some POCO classes...

    Sub Main()

    Dim ls As New List(Of Object)
    Dim lsGroup As New List(Of Object)
    Dim ran As New Random(Now.Millisecond)

    '' build a sample collection
    For x As Integer = 1 To 100
        ls.Add(New With {.ID = x, .DateAdded = Now.AddMinutes(-(ran.Next(1, 100000)))})
    Next

    '' now group them into years and months
    For Each item In ls
        Dim currentItem As Object = lsGroup.Where(Function(o) o.Year = item.DateAdded.Year And o.Month = item.DateAdded.Month).SingleOrDefault()
        If currentItem Is Nothing Then
            '' create
            Dim var = New With {.Year = item.DateAdded.Year, .Month = item.DateAdded.Month, .ItemCollection = New List(Of Object)}
            var.ItemCollection.Add(item)
            lsGroup.Add(var)
        Else
            '' add
            currentItem.ItemCollection.Add(item)
        End If
    Next

    '' display the results
    For Each group In lsGroup
        Console.WriteLine(group.Year & " - " & MonthName(group.Month))
        For Each item In group.ItemCollection
            Console.WriteLine(" > " & item.ID & " - " & item.DateAdded.ToString())
        Next
        Console.WriteLine()
    Next

    Console.ReadLine()

End Sub

Here's what I do when I need the month that's in a timestamp or date item called "t".

TIMESTAMP(DATE_FORMAT(t,'%y-%m-01'))

This returns another timestamp that represents midnight on the first day of that month.

Works for weeks too.

TIMESTAMP(FROM_DAYS(TO_DAYS(t) -MOD(TO_DAYS(t) -1, 7)))

This obscure incantation returns a timestamp that represents midnight on the Sunday preceding the given timestamp.