CODEWITHSUNDEEP
phpmysql
william
william

Reputation: 606

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 show me examples, or maybe some tutorials how to do?

Thx

Upvotes: 0

Views: 205

Answers (5)

O. Jones
O. Jones

Reputation: 108841

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.

Upvotes: 0

gg.
gg.

Reputation: 658

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

Upvotes: 0

MaxVT
MaxVT

Reputation: 13244

Something along the lines of this, perhaps?

SELECT * FROM table GROUP BY MONTH(dateColumn)

SELECT * FROM table WHERE MONTH(dateColumn) = 9

Upvotes: 2

bbb
bbb

Reputation: 702

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

Upvotes: 1

Bobby
Bobby

Reputation: 11576

Are you looking for the MySQL MONTH() Function?

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

Upvotes: 0

Related Questions