I am using cms 3.2.1.

I followed the tutorial videos and for the sql part I decided to use DB query to get dates and store the outcome in $result. As it still counts the number of dates. I believe my query works fine on retrieving data but can't seem to split year and month. Here is the code:

$list = ArrayList::create();
$result = DB::query("SELECT Date, DATE_FORMAT(Date, '%Y-%M-%m') FROM ArticlePage_Live AS DateString ORDER BY Date ASC");

if($result) {
while($record = $result->nextRecord()) {
list($year, $monthName, $monthNumber) = explode('-', $record['DateString']);

$list->push(ArrayData::create(array(
'Year' => $year,
'MonthName' => $monthName,
'MonthNumber' => $monthNumber,
'Link' => $this->Link("date/$year/$monthNumber"),
'ArticleCount' => ArticlePage::get()->where("
DATE_FORMAT(`Date`,'%Y%m') = '{$year}{$monthNumber}'
AND ParentID = {$this->ID}
")->count()
)));
}
}

Hope someone has the answer. Thanks in advance.

Looks like you put the AS DateString at the wrong location: this way it becomes the alias for the table name... Try:

$result = DB::query("SELECT Date, DATE_FORMAT(Date, '%Y-%M-%m') AS DateString FROM ArticlePage_Live ORDER BY Date ASC")